using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;
using System.Web.Mail;
using MBLocale;

public partial class Workflow_Item : System.Web.UI.Page
{
    DataSet dsWorkflow = new DataSet();
    DataSet dsInputInfo = new DataSet();
    HDDatabase HDD = null;
    string sWorkflowHistory_ref = "";
    ArrayList currencyCode_Rate = new ArrayList();
    int batchSize = 2;
    protected void Page_PreInit(object sender, EventArgs e)
    {
        this.Theme = Session["Theme"].ToString();
        this.Title = "MaxBlox";
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            security hdds = new security();

            if (Session["userid"] == null) Response.Redirect(hdds.SessionExpiryURL);
            if (Session["userid"].ToString() == "") Response.Redirect(hdds.SessionExpiryURL);

            if (Request.QueryString["mn"] != null)
                hdds.logMenuClick(Request.QueryString["mn"].ToString());

            //Code added by Sampath on 18-Nov-2011 for IPP Vertical privilege
            if (Request.QueryString["mn"] != null)
            {
                if (!hdds.checkObjectMenuAccess(Request.QueryString["mn"]))
                {
                    tblmain.Visible = false;
                    tblbtn.Visible = false;
                    lblmessage.Text = wrapItAsMessage("<img src=\"Images/info.gif\" alt=\"\"></img>&nbsp;You do not have sufficient access permission.", true);
                    return;
                }
            }

            Session["datasort"] = hfSort.Value; // srivatsan
            Session["datasortorder"] = hfSortOrder.Value; // srivatsan

            if (Session["cp"] != null)
            {
                hihCurrentPageId.Value = Session["cp"].ToString();
                Session.Remove("cp");
            }

            if (hf_currentpage.Value == "0")
            {
                if (Request.QueryString["cp"] != null)
                {
                    hihCurrentPageId.Value = Request.QueryString["cp"];
                    hf_currentpage.Value = "1";
                }
            }

            hdds = null;

            btnApproveAll.Enabled = false;
            Navigator.storeURL(Request.Url.ToString(), IsPostBack, false, true);
            batchSize = GlobalValues.intNoOfRecordsPerPage;
            HDD = new HDDatabase();




            //getTable();
            loadTables();

            //filldropdowndefault();
            getWorkItem();




            // Added by srivatsan
            //if (Page.IsPostBack)
            //{
            //try
            //{
            //    ddcreatedby.SelectedValue = Request.Form["ctl00$ContentPlaceHolder3$ddcreatedby"];
            //    ddobject.SelectedValue = Request.Form["ctl00$ContentPlaceHolder3$ddobject"];

            //    ddcreatedat.SelectedValue = Request.Form["ctl00$ContentPlaceHolder3$ddcreatedat"];


            //    ddcreatedby.SelectedValue = this.Form.Page.Request.Form["ctl00$ContentPlaceHolder3$ddcreatedby"];
            //    ddobject.SelectedValue = Request.Form["ctl00$ContentPlaceHolder3$ddobject"];
            //    ddcreatedat.SelectedValue = Request.Form["ctl00$ContentPlaceHolder3$ddcreatedat"];
            //   // this.Form.Page.Request.Form["ctl00$ContentPlaceHolder3$ddcreatedby"];
            //}
            //catch (Exception ex)
            //{
            //    LogWriter.WriteLog("Error dropdown fill " + ex.Message);
            //}
            //}



            if (!IsPostBack)
            {
                displayHistory();
                //loadobjectscombo(); // Added by srivatsan

            }
            //loadfilter(); // added by srivatsan



            //ddobject.Attributes.Add("OnChange", "resettoZero(); submitform(); alert('end');");
            //ddcreatedby.Attributes.Add("OnChange", "resettoZero(); submitform(); alert('end');");
            //ddcreatedat.Attributes.Add("OnChange", "resettoZero(); submitform(); alert('end');");


        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("Page_Load : Error : " + Ex.Message);
        }
    }

    private void getPageInfo(ref DataSet dsPageInfo)
    {
        try
        {
            PageInfoLoad PIL = new PageInfoLoad();
            dsPageInfo = PIL.GetPageSetPageCtrlInfo("eed8935a7731fd44"); // workflow_history
            DataSet dsTemp = new DataSet();
            dsTemp = PIL.GetPageSetPageCtrlInfo("7eec6ff1641256a2"); // workitem_user
            dsPageInfo.Tables["Page"].Merge(dsTemp.Tables["Page"]);
            dsPageInfo.Tables["PageCtrl"].Merge(dsTemp.Tables["PageCtrl"]);
            dsPageInfo.Tables["PageKeys"].Merge(dsTemp.Tables["PageKeys"]);
            dsTemp = null;
            PIL = null;
        }
        catch (Exception Ex)
        {
            LogWriter.WriteLog("getPageInfo : Error : " + Ex.Message);
        }

    }

    //getting all the necessary table information
    private void getTable()
    {
        try
        {
            string sSql = "Select * from sy_workitem_user";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_workitem_user").Copy());

            string sUserDisplayName = getUserDisplayName();
            sSql = "select *, " + sUserDisplayName + " as display_name from sy_usr";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_usr").Copy());
            sSql = "select * from sy_org_structure";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_org_structure").Copy());
            sSql = "select * from sy_mnu";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_mnu").Copy());
            sSql = "select * from sy_pgs";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_pgs").Copy());
            sSql = "select * from sy_workflow_rules";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_workflow_rules").Copy());
            sSql = "select * from sy_workflow_ruleset";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_workflow_ruleset").Copy());
            sSql = "select * from sy_workflow_history";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_workflow_history").Copy());
            sSql = "Select * from sy_obj";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_obj").Copy());
            sSql = "Select * from sy_obj_att";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_obj_att").Copy());
            sSql = "Select * from sy_usr_rol";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_usr_rol").Copy());
            //sSql = "Select * from sy_obj_acc";
            sSql = "Select * from sy_obj_acc where role_id ='" + Session["roleid"].ToString() + "'";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_obj_acc").Copy());
            sSql = "Select * from sy_work_item";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_work_item").Copy());

            sSql = "Select * from sy_field_updates";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_field_updates").Copy());
            sSql = "Select * from sy_reject_field_updates";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_reject_field_updates").Copy());
            //Added by John Sathish
            sSql = "Select * from sy_workflow_action";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_workflow_action").Copy());
            //Up to this

            sSql = "select * from sy_bnd";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_bnd").Copy());
            sSql = "select * from sy_pg_ctr";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_pg_ctr").Copy());

            sSql = "select * from sy_pg";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_pg").Copy());
            sSql = "select * from sy_workitem_additional_info";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_workitem_additional_info").Copy());

            DynamicDisplayI DDI = new DynamicDisplayI();
            ArrayList cols = new ArrayList();
            cols.Add("c_rate"); cols.Add("c_code");
            currencyCode_Rate = DDI.getUserSetting("sy_usr", "currency_id", "sy_currency", cols, "c_code");
            currencyCode_Rate.Add(DDI.getCorporateCurrency());
            DDI = null;
        }
        catch (Exception Ex)
        {
            LogWriter.WriteLog("getTable : Error : " + Ex.Message);
        }
    }

    private void getWorkItem()
    {
        try
        {
            DynamicDisplayI DDI = new DynamicDisplayI();
            ArrayList cols = new ArrayList();
            cols.Add("c_rate"); cols.Add("c_code");
            currencyCode_Rate = DDI.getUserSetting("sy_usr", "currency_id", "sy_currency", cols, "c_code");
            currencyCode_Rate.Add(DDI.getCorporateCurrency());
            DDI = null;

            addHeader();
            DataTable dtItem = dsWorkflow.Tables["workitem"];


            //// Added by srivatsan

            //processDataSet(ref dtItem);
            //dtItem = processWorkItemOrder(ref dtItem);
            ////

            addRows(ref dtItem);
        }
        catch (Exception Ex)
        {
            LogWriter.WriteLog("getWorkItem : Error : " + Ex.Message);
        }
    }

    private void addHeader()
    {
        try
        {
            string sUsr_Rol = Session["roleid"].ToString(); //getColumnValue("sy_usr_rol", "user_id", Session["user_afrowid"].ToString(), "role_id");
            string sCreate = getColumnValue("sy_obj_acc", "role_id='" + sUsr_Rol + "' and object_id", "ob_workflow_history", "create");

            TableRow tr = new TableRow();
            CheckBox chk = new CheckBox();
            chk.ID = "chk_";
            chk.Attributes.Add("onclick", "SelectDeselectAll()");
            //            chk.CheckedChanged += new EventHandler(chk_CheckedChanged);
            //            chk.AutoPostBack = true;

            TableCell htc = new TableCell();
            htc.HorizontalAlign = HorizontalAlign.Left;
            htc.Controls.Add(chk);
            tr.Cells.Add(htc);
            // modifeid  by srivatsan for the link filter
            tr.Cells.Add(getHeaderCell("&nbsp;Workitem Message"));
            tr.Cells.Add(getHeaderCell("Description"));
            tr.Cells.Add(getHeaderCell("Status"));
            tr.Cells.Add(getHeaderCell("Comments"));
            tr.Cells.Add(getHeaderCell("By"));
            tr.Cells.Add(getHeaderCell("Dated"));
            if (sCreate.ToLower() == "true")
                tr.Cells.Add(getCell("&nbsp;"));

            tr.CssClass = "Grid_Header";
            tr.Width = Unit.Percentage(100);
            Table1.Rows.Add(tr);
            tr = null;
        }
        catch (Exception Ex)
        {
            LogWriter.WriteLog("addHeader : Error : " + Ex.Message);
        }
    }

    private void CheckBoxChanged(ControlCollection frmControl, bool Checked)
    {
        try
        {
            foreach (Control con in frmControl)
            {
                if (con is CheckBox)
                {
                    CheckBox chk = (CheckBox)con;
                    chk.Checked = Checked;

                }
                else
                    CheckBoxChanged(con.Controls, Checked);
            }
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("CheckBoxChanged : Error : " + Ex.Message);
        }
    }

    void chk_CheckedChanged(object sender, EventArgs e)
    {
        CheckBox chk = (CheckBox)sender;
        CheckBoxChanged(this.Controls, chk.Checked);
        if (chk.Checked)
            hf_selected.Value = "1";
        else
            hf_selected.Value = "0";
    }

    private DataTable processWorkItem(ref DataTable dtItem)
    {
        DataTable dtWorkItem = dtItem.Clone();
        try
        {

            DataRow[] drr = null;


            // Added by srivatsan
            string sqlfilter = "";

            if (ddobject.SelectedIndex > 0)
            {
                sqlfilter += " and object_id = '" + ddobject.SelectedValue + "'";
            }
            if (ddcreatedby.SelectedIndex > 0)
            {
                sqlfilter += " and object_record_created_by='" + ddcreatedby.SelectedValue + "'";
            }
            if (ddcreatedat.SelectedIndex > 0)
            {
                string startdate = ddcreatedat.SelectedValue + " 00:00:00";
                string enddate = ddcreatedat.SelectedValue + " 23:59:59";
                sqlfilter += " and (object_record_created_at >= '" + startdate + "' and object_record_created_at <= '" + enddate + "')";
            }
            // end of added by srivatsan

            if (Session["user_afrowid"].ToString() == "650a333596bbf348")
            {

                //if (ddobject.SelectedIndex < 1)
                // {
                drr = dtItem.Select("for_user='" + Session["user_afrowid"].ToString() + "' or for_user='' or for_user is null " + sqlfilter + "", "created_at desc"); // modified by srivatsan before it was desc
                //}
                // else
                // {
                //   drr = dtItem.Select("for_user='" + Session["user_afrowid"].ToString() + "' or for_user='' or for_user is null and (object_id='"+ddobject.SelectedValue+"')", "created_at asc"); // modified by srivatsan before it was desc
                // }
            }
            else
            {
                // Modified by srivatsan for filter in workitem
                //if (ddobject.SelectedIndex < 1)
                //{


                drr = dtItem.Select("for_user='" + Session["user_afrowid"].ToString() + "' " + sqlfilter + "", "created_at desc"); // Modified by srivatsan before it was desc
                // }
                //else
                //{
                // drr = dtItem.Select("for_user='" + Session["user_afrowid"].ToString() + "' and object_id='" + ddobject.SelectedValue + "'", "created_at asc"); // Modified by srivatsan before it was desc
                // }
            }
            getTableFromArray(ref dtWorkItem, drr);

            drr = null;
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("processWorkItem : Error : " + Ex.Message);
        }
        return dtWorkItem;
    }

    private void addRows(ref DataTable dtItem)
    {
        try
        {
            DynamicDisplayI DDI = new DynamicDisplayI();
            //getting the users timezone setting
            string sUTC_Offset = DDI.getUserSetting("sy_usr", "time_zone", "sy_timezone", "utc_offset", "timezone_id");
            //getting the object access info
            string sUsr_Rol = Session["roleid"].ToString(); //getColumnValue("sy_usr_rol", "user_id", Session["user_afrowid"].ToString(), "role_id");
            string sCreate = getColumnValue("sy_obj_acc", "role_id='" + sUsr_Rol + "' and object_id", "ob_workflow_history", "create");
            int iClassCount = 0;
            for (int iRowCount = 0; iRowCount < dtItem.Rows.Count; iRowCount++)
            {
                TableRow htr = new TableRow();
                TableRow htr1 = new TableRow();
                getRow(dtItem.Rows[iRowCount], ref htr, sUTC_Offset, DDI, sUsr_Rol, sCreate);

                if (htr.Cells.Count == 0) continue;
                int iClass = iClassCount % 2;
                htr.Attributes.Add("Class", "Grid_Row" + iClass.ToString());
                Table1.Rows.Add(htr);


                // Added by srivatsan
                getInfoRow(dtItem.Rows[iRowCount], ref htr1, sUTC_Offset);
                if (htr1.Cells.Count == 0) continue;
                //int iClass = iClassCount % 2;
                htr1.Attributes.Add("Class", "Grid_Row" + iClass.ToString());

                Table1.Rows.Add(htr1);
                // End of added by srivatsan



                iClassCount++;
            }

            if (Table1.Rows.Count == 1)
                addNoRecordsRow();
            DDI = null;
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("addRows : Error : " + Ex.Message);
        }
    }


    // Added by srivatsan
    private void getInfoRow(DataRow dr, ref TableRow htr, string offset)
    {
        try
        {
            // LogWriter.WriteLog("Start of New Row");
            Table btTable = new Table();
            TableRow btRow = new TableRow();
            TableCell btCell = new TableCell();
            btTable.BorderStyle = BorderStyle.None;
            //btTable.BackColor = System.Drawing.Color.CornflowerBlue;
            btTable.CellPadding = 2;
            btTable.CellSpacing = 0;


            LiteralControl LitCon = new LiteralControl();
            LitCon.ID = "Lit" + dr["af_row_id"].ToString();
            //string divid = "DIV" + dr["af_row_id"].ToString();
            //string lblid = "LBL" + dr["af_row_id"].ToString();
            string workitemdisplay = getMoreInfoData(dr["ruleset_id"].ToString(), dr["af_row_id"].ToString(), dr["object_id"].ToString(), dr["key_value"].ToString(), offset);


            //LitCon.Text = "<DIV ID= " + divid + " style= font-size: 2px; Display: none; WIDTH: 200px;>" + workitemdisplay + "</DIV>";
            LitCon.Text = workitemdisplay;
            btCell.Controls.Add(LitCon);
            btCell.Font.Size = FontUnit.Point(7);
            btCell.CssClass = "defaultFont";
            //btCell.Font.Underline = true;
            btRow.Cells.Add(btCell);
            Literal lt = new Literal();
            lt.Text = "&nbsp;";

            btTable.Rows.Add(btRow);
            btCell = new TableCell();
            btCell.Controls.Add(btTable);

            htr.VerticalAlign = VerticalAlign.Middle;
            btCell.ColumnSpan = 8;
            btCell.Attributes.Add("align", "left");
            btCell.Attributes.Add("width", "100%");
            htr.Cells.Add(btCell);
            // LogWriter.WriteLog("End of New Row");


        }
        catch (Exception Ex)
        {
            LogWriter.WriteLog("getInfoRow : Error : " + Ex.Message);
        }
    }

    // Gets Additional Data from sy_workitem_additional_info and shows as seperate line

    private string getMoreInfoData(string rulesetid, string af_row_id, string object_id, string keyvalue, string utcoffset)
    {
        //LogWriter.WriteLog("Start of getmoredata");
        //string result = "";
        StringBuilder result = new StringBuilder();
        //DataSet ds = new DataSet();
        DataSet ds1 = new DataSet();
        DataSet dsbind = new DataSet();


        string userdisplayname = getUserDisplayName();
        DataRow[] dr = dsWorkflow.Tables["sy_workitem_additional_info"].Select("ruleset_id='" + rulesetid + "'", "seq");

        //string myquery = "select ";
        if (dr.Length > 0)
        {

            string objname = getColumnValue("sy_obj", "af_row_id", object_id, "object_id");
            string page_id = getColumnValue("sy_pg", "object_id", objname, "af_row_id");
            string tablename = getColumnValue("sy_pg", "af_row_id", page_id, "page_table_name");
            string getvalue = "Select * from " + tablename + " where af_row_id = '" + keyvalue + "'";

            DataRow datarow = getDataRow(tablename, "af_row_id", keyvalue);
            string[] controlid = new string[dr.Length];
            string[] controlname = new string[dr.Length];
            DataRow[] drinterdisplayname = new DataRow[dr.Length];
            for (int i = 0; i < dr.Length; i++)
            {
                DataRow[] drdisplayname = dsWorkflow.Tables["sy_pg_ctr"].Select("page_id='" + page_id + "' and control_attrib_id='" + dr[i]["attrib_id"].ToString() + "'");
                controlid[i] = dr[i]["attrib_id"].ToString();
                controlname[i] = drdisplayname[0]["control_name"].ToString();

                drinterdisplayname[i] = drdisplayname[0];
            }


            for (int j = 0; j < controlid.Length; j++)
            {

                if (dsWorkflow.Tables[tablename].Columns.Contains(controlid[j].ToString()))
                {
                    if (drinterdisplayname[j]["control_bind_id"].ToString() != string.Empty)
                    {
                        string bindtable = getColumnValue("sy_bnd", "af_row_id", drinterdisplayname[j]["control_bind_id"].ToString(), "bind_table");
                        string bindtext = getColumnValue("sy_bnd", "af_row_id", drinterdisplayname[j]["control_bind_id"].ToString(), "bind_text");
                        string bindvalue = getColumnValue("sy_bnd", "af_row_id", drinterdisplayname[j]["control_bind_id"].ToString(), "bind_value");
                        string value = "";
                        //string value = HDD.GetColumnValue(bindtable, bindtext, "" + bindvalue + "='" + datarow[controlid[j].ToString()].ToString() + "'");

                        string getquery = "select " + bindtext + " as bindtext from " + bindtable + " where " + bindvalue + "= '" + datarow[controlid[j].ToString()].ToString() + "'";
                        DataSet dsbinddata = new DataSet();
                        dsbinddata = HDD.GetDataset(getquery);
                        try
                        {
                            value = dsbinddata.Tables[0].Rows[0]["bindtext"].ToString();
                        }
                        catch
                        {
                            value = "";
                        }


                        //result += controlname[j].ToUpper() + ": " + value + ";" + "&nbsp;&nbsp;";
                        result.Append(controlname[j].ToUpper() + ": " + value + ";" + "&nbsp;&nbsp;&nbsp;");

                    }
                    else
                    {

                        if (controlid[j].ToLower() == "created_by" || controlid[j].ToLower() == "modified_by")
                        {
                            string userid = getColumnValue("sy_usr", "af_row_id", datarow[controlid[j].ToString()].ToString(), userdisplayname);
                            //result += controlname[j].ToString().ToUpper() + ": " + userid + ";" + "&nbsp;&nbsp;";
                            result.Append(controlname[j].ToString().ToUpper() + ": " + userid + ";" + "&nbsp;&nbsp;&nbsp;");
                        }
                        else
                        {
                            //result += controlname[j].ToUpper() + ": " + datarow[controlid[j].ToString()].ToString() + ";" + "&nbsp;&nbsp;";
                            result.Append(controlname[j].ToUpper() + ": " + getValueWRTDataType(datarow[controlid[j].ToString()].ToString(), drinterdisplayname[j]["control_attrib_type"].ToString(), utcoffset) + ";" + "&nbsp;&nbsp;&nbsp;");
                        }
                    }
                }
            }

        }
        // LogWriter.WriteLog("End of getdata");

        return result.ToString();
    }



    private void addNoRecordsRow()
    {
        try
        {
            TableRow htr = new TableRow();
            htr.Cells.Add(getCell("&nbsp;No Workitems available."));
            htr.Cells[0].ColumnSpan = 7;
            htr.Attributes.Add("Class", "Grid_Row1");
            Table1.Rows.Add(htr);
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("addNoRecordsRow : Error : " + Ex.Message);
        }
    }

    private string getValueWRTDataType(string Value, string DataType, string UTC_Offset)
    {
        string sReturn = "";
        try
        {
            switch (DataType.ToLower())
            {
                case "dt":
                    Localization li = new Localization();
                    sReturn = li.getDateTime4TimeZone(Value, UTC_Offset, false, true);
                    //sReturn = li.getDateTime4TimeZone(Value, UTC_Offset, false, false);
                    li = null;
                    break;
                case "da":
                    #region Old code commented by 'Imran Gani' on 28-Feb-2014, for MBLocale
                    //DateTime dt = DateTime.Parse(Value);
                    //Localization lo = new Localization();
                    //sReturn = dt.ToString(lo.getDateFormat());
                    //lo = null;
                    #endregion
                    sReturn = MBLocalization.getLocalizedDate(HttpContext.Current.Session["userlocale"].ToString(), DateTime.Parse(Value));
                    break;
                case "cu":
                    DynamicDisplayI DDI = new DynamicDisplayI();
                    sReturn = DDI.manageCurrency(Value, currencyCode_Rate);
                    DDI = null;
                    break;
                case "an":
                case "nu":
                    sReturn = MBLocalization.getLocalizedInteger(HttpContext.Current.Session["userlocale"].ToString(), Convert.ToInt32(Value));
                    break;
                case "dc":
                case "fl":
                    sReturn = MBLocalization.getLocalizedDecimal(HttpContext.Current.Session["userlocale"].ToString(), Convert.ToDouble(Value));
                    break;
                default:
                    sReturn = Value;
                    break;
            }
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("getValueWRTDataType : Error : " + Ex.Message);
            sReturn = Value;
        }
        return sReturn;
    }

    private bool GetObjectTable(string ObjectName)
    {
        if (!dsWorkflow.Tables.Contains(ObjectName))
        {
            string sSql = "Select * from " + ObjectName.Replace("'", "''");
            DataTable dtData = HDD.GetDataTable(sSql, ObjectName);
            dsWorkflow.Tables.Add(dtData.Copy());
            dtData.Dispose();
            dtData = null;
        }
        return true;
    }

    private string replaceVariable(string Value, string Object_Id, string Key_Value, string UTC_Offset)
    {
        string sReturn = "";
        try
        {
            sReturn = Value;

            string sObject_Table = getColumnValue("sy_obj", "af_row_id", Object_Id, "object_table");
            GetObjectTable(sObject_Table);

            //string sSql = "Select * from " + sObject_Table + " where af_row_id='" + Key_Value + "'";
            //DataRow drData = HDD.GetTableRow(sSql);
            string sObject_id = getColumnValue("sy_obj", "af_row_id", Object_Id, "object_id");

            DataRow drData = getDataRow(sObject_Table, "af_row_id", Key_Value);

            //if (drData != null)
            //{
            DataRow[] drr = dsWorkflow.Tables["sy_obj_att"].Select("object_id = '" + Object_Id + "'");
            if (drData == null)
            {
                foreach (DataRow dr in drr)
                {
                    sReturn = sReturn.ToLower().Replace("[" + sObject_id.ToLower() + "." + dr["attrib_id"].ToString().ToLower() + "]", " Record not found");//sReturn = sReturn.ToLower().Replace("[" + sObject_id.ToLower() + "." + dr["attrib_id"].ToString().ToLower() + "]", " ");
                }
                sReturn = "Record not found";
            }
            else
            {
                foreach (DataRow dr in drr)
                {
                    if (sReturn.ToLower().Contains("[" + sObject_id.ToLower() + "." + dr["attrib_id"].ToString().ToLower() + "]"))
                        sReturn = sReturn.ToLower().Replace("[" + sObject_id.ToLower() + "." + dr["attrib_id"].ToString().ToLower() + "]", getValueWRTDataType(drData[dr["attrib_id"].ToString()].ToString(), dr["attrib_data_type"].ToString(), UTC_Offset));

                    //sReturn = sReturn.ToLower().Replace("[" + sObject_id.ToLower() + "." + dr["attrib_id"].ToString().ToLower() + "]", drData[dr["attrib_id"].ToString()].ToString());
                }
            }
            //}
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("replaceVariable : Error : " + ex.Message);
        }

        return sReturn;
    }

    private string getLink(DataRow dr, string Usr_rol, string UTC_Offset)
    {
        string EditLink = "";
        try
        {
            string sObject_id = getColumnValue("sy_obj", "af_row_id", dr["object_id"].ToString(), "object_id");
            string sRead = getColumnValue("sy_obj_acc", "role_id='" + Usr_rol + "' and object_id", sObject_id, "read");
            string sEdit = getColumnValue("sy_obj_acc", "role_id='" + Usr_rol + "' and object_id", sObject_id, "edit");

            string sRule_Id = dr["rule_id"].ToString();

            string sWorkMessage = getColumnValue("sy_work_item", "rule_id", sRule_Id, "message");
            sWorkMessage = replaceVariable(sWorkMessage, dr["object_id"].ToString(), dr["key_value"].ToString(), UTC_Offset);
            if (!sWorkMessage.ToLower().Contains("record not found"))
            {
                if (sRead.ToLower() == "true")
                {
                    //string sMenu = getColumnValue("sy_mnu", "af_row_id", dr["menu_id"].ToString(), "menu_id");
                    EditLink = @"&nbsp;<a href=""" + getEditPage(dr["menu_id"].ToString()) + @"?pgs_id="
                        + dr["pageset_id"].ToString().ToString() + "&PK=" + HttpUtility.UrlEncode(dr["key_value"].ToString())
                        //+ getColumnValue("sy_pgs", "af_row_id", dr["pageset_id"].ToString().ToString(), "pageset_id") + "&PK=" + HttpUtility.UrlEncode(dr["key_value"].ToString())
                        + "&mn=" + dr["menu_id"].ToString();


                    //EditLink = @"&nbsp;<a href=""" + getEditPage(sMenu) + @"?pgs_id="
                    //    + getColumnValue("sy_pgs", "af_row_id", dr["pageset_id"].ToString().ToString(), "pageset_id") + "&PK=" + HttpUtility.UrlEncode(dr["key_value"].ToString())
                    //    + "&mn=" + sMenu + "&cp=" + hihCurrentPageId.Value + "";
                    // Added by srivatsan


                    if (sEdit.ToLower() != "true")
                    {
                        EditLink += "&VMODE=1";
                    }
                    EditLink += @"&WF=1""><font color=""blue"">"
                        + sWorkMessage /*getColumnValue("sy_usr", "af_row_id", dr["created_by"].ToString().ToString(), "first_name") + " " + getColumnValue("sy_usr", "af_row_id", dr["created_by"].ToString().ToString(), "last_name")*/ + "</font></a>";
                }
                else
                    EditLink = "&nbsp;" + sWorkMessage; /*getColumnValue("sy_usr", "af_row_id", dr["created_by"].ToString().ToString(), "first_name") + " " + getColumnValue("sy_usr", "af_row_id", dr["created_by"].ToString().ToString(), "last_name")*/
            }
            else
            {
                EditLink = "&nbsp;Record not found";
            }
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("getLink : Error : " + Ex.Message);
        }
        return EditLink;
    }

    private string getUserDisplayName()
    {
        string userDisplayField = HDD.GetColumnValue("sy_acr_mem", "mem_id", "af_row_id='30583eb791cb7d5b'");
        userDisplayField = userDisplayField.Trim().Length == 0 ? "user_id" : userDisplayField;
        return userDisplayField;
    }

    private void getRow(DataRow dr, ref TableRow htr, string Utc_Offset, DynamicDisplayI DDI, string Usr_rol, string History_Acc)
    {
        try
        {
            string EditLink = getLink(dr, Usr_rol, Utc_Offset);
            //if the corresponding ruleset is not found abort the workflow
            if (getColumnValue("sy_workflow_ruleset", "af_row_id", dr["ruleset_id"].ToString().ToString(), "af_row_id") == "")
                EditLink = "&nbsp;Ruleset not found";

            TableCell htc = new TableCell();
            CheckBox chk = new CheckBox();
            chk.ID = "chk_" + dr["af_row_id"].ToString();
            chk.Attributes.Add("name", "chk_App");
            //chk.Attributes.Add("click", "select('" + chk.ID + "')");
            htc.Controls.Add(chk);
            htr.Cells.Add(htc);
            htc = new TableCell();
            if (dr["is_active"].ToString().ToLower() == "true")
            {
                htc.Text = EditLink;
                htr.Cells.Add(htc);

                htc = new TableCell();
                //htc.Text = getColumnValue("sy_workflow_ruleset", "af_row_id", dr["ruleset_id"].ToString().ToString(), "name");
                // commented by srivatsan
                htc.Text = dr["temp_description"].ToString();

                string sTemp = getColumnValue("sy_workflow_ruleset", "af_row_id", dr["ruleset_id"].ToString().ToString(), "description");
                if (sTemp != "")
                    htc.Text += "-" + sTemp;
                htr.Cells.Add(htc);

                htc = null;

                string sUserDisplayName = getUserDisplayName();

                foreach (DataColumn dc in dr.Table.Columns)
                {
                    switch (dc.ColumnName.ToLower())
                    {
                        case "created_by":
                            string sValue = "&nbsp;";
                            if (dr[dc.ColumnName] != System.DBNull.Value)
                            {
                                sValue = dr["temp_by"].ToString();  // added by srivatsan on MAR 03 2009
                                // Commented by srivatsan on Mar 03 2009
                                //sValue = getColumnValue("sy_usr", "af_row_id", dr["created_by"].ToString().ToString(), "display_name"); 
                                ////sValue = getColumnValue("sy_usr", "af_row_id", dr["created_by"].ToString().ToString(), "last_name");
                                ////if (sValue.Trim() == "")
                                ////    sValue = getColumnValue("sy_usr", "af_row_id", dr["created_by"].ToString().ToString(), "first_name");
                            }
                            htc = getCell(sValue);
                            break;
                        case "created_at":
                            //case "modified_at":
                            sValue = "&nbsp;";
                            if (dr[dc.ColumnName] != System.DBNull.Value)
                            {
                                sValue = dr["temp_dated"].ToString(); //added by srivatsan
                                //commented by srivatsan
                                Localization li = new Localization();
                                sValue = li.getDateTime4TimeZone(dr[dc.ColumnName].ToString(), Utc_Offset, false, true);
                                //sValue = li.getDateTime4TimeZone(dr[dc.ColumnName].ToString(), Utc_Offset, false, true);
                                li = null;
                            }
                            htc = getCell(sValue);
                            break;
                        case "comments":
                            sValue = "&nbsp;";
                            if (dr[dc.ColumnName] != System.DBNull.Value)
                            {
                                sValue = dr[dc.ColumnName].ToString();
                            }
                            htc = getCell(sValue);
                            break;
                        case "status":
                            switch (dr["status"].ToString().ToLower())
                            {
                                case "submitted":
                                    htc = getCell("Submitted");
                                    break;
                                case "AS":
                                    htc = getCell("Submitted");
                                    break;
                                case "approved":
                                    htc = getCell("Approved");
                                    break;
                                case "rejected":
                                    htc = getCell("Rejected");
                                    break;
                            }

                            break;
                    }
                    if (htc != null)
                    {
                        htr.Cells.Add(htc);
                    }
                }

                if (getColumnValue("sy_workflow_ruleset", "af_row_id", dr["ruleset_id"].ToString(), "af_row_id") == "" || dr["is_active"].ToString().ToLower() == "false" || dr["for_user"].ToString() == "" || EditLink.ToLower().Contains("record not found")) // orphaned workitem records displayed for admin
                {
                    getCloseButton(dr, ref htr);
                }
                else
                {
                    if (History_Acc.ToLower() == "true")
                    {
                        if (dr["status"].ToString().ToLower() == "submitted")
                            getButtons(dr, ref htr);
                        else
                        {
                            string sSequence = getColumnValue("sy_workflow_rules", "af_row_id", dr["rule_id"].ToString(), "sequence");
                            if (sSequence == "")
                                sSequence = "0";
                            int iSeq = int.Parse(sSequence);
                            if (iSeq != 0)
                                getButtons(dr, ref htr, "");
                            else
                            {
                                getCloseButton(dr, ref htr);
                            }
                        }
                    }
                    else
                    {
                        htr.Cells.Add(getCell("&nbsp;"));
                        htr.Height = Unit.Parse("15pt");
                    }
                }
            }
            else if (dr["is_active"].ToString().ToLower() == "false")
            {
                htc = getCellNotActive("&nbsp;Work item cancelled due to re-submission");
                htr.Cells.Add(htc);
                if (History_Acc.ToLower() == "true")
                    getCloseButton(dr, ref htr);
                htr.Height = Unit.Parse("15pt");
            }
        }
        catch (Exception Ex)
        {
            LogWriter.WriteLog("getRow : Error : " + Ex.Message);
        }
    }

    private TableCell getCell(string ColumnValue)
    {
        TableCell htc = new TableCell();
        htc.Text = ColumnValue;
        return htc;


    }


    private TableCell getCellNotActive(string ColumnValue)
    {
        TableCell htc = new TableCell();
        htc.Text = ColumnValue;
        htc.ColumnSpan = 6;
        return htc;
    }
    private void getButtons(DataRow dr, ref TableRow htr)
    {
        try
        {
            bool isRulesetPresent = true;
            if (getColumnValue("sy_workflow_ruleset", "af_row_id", dr["ruleset_id"].ToString().ToString(), "af_row_id") == "")
                isRulesetPresent = false;
            Table btTable = new Table();
            TableRow btRow = new TableRow();
            TableCell btCell = new TableCell();
            btTable.BorderStyle = BorderStyle.None;
            btTable.CellPadding = 2;
            btTable.CellSpacing = 0;
            ImageButton btnAcc = new ImageButton();
            //Button btnAcc = new Button();
            btnAcc.ID = "App" + dr["af_row_id"].ToString();
            btnAcc.ImageUrl = "Images/approve.gif";
            btnAcc.AlternateText = "Approve";
            btnAcc.Attributes.Add("key", dr["af_row_id"].ToString());
            btnAcc.CausesValidation = true;
            //btnAcc.OnClientClick = "javascript:disableThis('ctl00_ContentPlaceHolder3_App" + dr["af_row_id"].ToString() + "','Approve'); return false;";
            btnAcc.Click += new ImageClickEventHandler(btnAcc_Click);

            ImageButton btnRej = new ImageButton();
            btnRej.ID = "Rej" + dr["af_row_id"].ToString();
            btnRej.AlternateText = "Reject";
            btnRej.ImageUrl = "Images/reject.gif";
            btnAcc.CausesValidation = true;
            btnRej.OnClientClick = "javascript:getComments('" + dr["af_row_id"].ToString() + "','Rejected');disableThis('ctl00_ContentPlaceHolder3_Rej" + dr["af_row_id"].ToString() + "','Reject'); return false;";
            btnRej.Click += new ImageClickEventHandler(btnRej_Click);
            TableCell tc = new TableCell();
            //tc.Width = Unit.Point(90);
            //tc.Controls.Add(btnAcc);
            if (!isRulesetPresent)
            {
                btnAcc.Enabled = false;
                btnRej.Enabled = false;
            }
            btnApproveAll.Enabled = true;
            btCell.Controls.Add(btnAcc);
            btRow.Cells.Add(btCell);
            Literal lt = new Literal();
            lt.Text = "&nbsp;";
            //tc.Controls.Add(lt);
            //htr.Cells.Add(tc);
            //tc = new TableCell();
            //tc.Controls.Add(btnRej);
            btCell = new TableCell();
            btCell.Controls.Add(btnRej);
            btRow.Cells.Add(btCell);
            btTable.Rows.Add(btRow);
            btCell = new TableCell();
            btCell.Controls.Add(btTable);

            htr.VerticalAlign = VerticalAlign.Middle;
            //htr.Cells.Add(tc);
            btCell.Attributes.Add("align", "right");
            htr.Cells.Add(btCell);
        }
        catch (Exception Ex)
        {
            LogWriter.WriteLog("getButtons : Error : " + Ex.Message);
        }
    }

    private void getCloseButton(DataRow dr, ref TableRow htr)
    {
        try
        {
            Table btTable = new Table();
            TableRow btRow = new TableRow();
            btTable.BorderStyle = BorderStyle.None;
            btTable.CellPadding = 2;
            btTable.CellSpacing = 0;
            ImageButton btnClose = new ImageButton();
            btnClose.ID = "Close" + dr["af_row_id"].ToString();
            btnClose.AlternateText = "Close";
            btnClose.ImageUrl = "Images/close.gif";
            btnClose.Attributes.Add("key", dr["af_row_id"].ToString());
            //btnClose.OnClientClick = "javascript:disableThis('ctl00_ContentPlaceHolder3_Close" + dr["af_row_id"].ToString() + "','Close');return false;";
            btnClose.Click += new ImageClickEventHandler(btnClose_Click);
            TableCell tc = new TableCell();
            Literal lt = new Literal();
            lt.Text = "&nbsp;";
            tc.Controls.Add(lt);
            tc.Width = Unit.Pixel(59);
            btRow.Cells.Add(tc);
            tc = new TableCell();
            tc.HorizontalAlign = HorizontalAlign.Right;
            //tc.Width = Unit.Point(90);
            tc.Controls.Add(btnClose);

            //tc.Controls.Add(lt);
            htr.VerticalAlign = VerticalAlign.Middle;
            btRow.Cells.Add(tc);
            btTable.Rows.Add(btRow);
            tc = new TableCell();
            tc.Controls.Add(btTable);
            tc.Attributes.Add("align", "right");
            htr.Cells.Add(tc);
        }
        catch (Exception Ex)
        {
            LogWriter.WriteLog("getCloseButton : Error : " + Ex.Message);
        }
    }

    void btnClose_Click(object sender, ImageClickEventArgs e)
    {
        try
        {
            disableImageButtons(this.Controls);
            ImageButton btn = (ImageButton)sender;
            string sKey_Value = btn.Attributes["key"];
            btn = null;
            DataRow drData = getWorkFlowHistoryRecord(sKey_Value);
            DataSet dsPageInfo = null;
            getPageInfo(ref dsPageInfo);
            //DataSet dsInputInfo = new DataSet();
            ScreenReader SR = new ScreenReader();
            SR.CreatePageDataTable(dsInputInfo, dsPageInfo.Tables["page"], dsPageInfo.Tables["pagectrl"]);
            DataRow dr = dsInputInfo.Tables["p_workflow_history"].NewRow();
            dr["af_row_id"] = SR.getHashKey();
            dr["ruleset_id"] = drData["ruleset_id"];
            dr["rule_id"] = drData["rule_id"];
            dr["object_id"] = drData["object_id"];
            dr["pageset_id"] = drData["pageset_id"];
            dr["menu_id"] = drData["menu_id"];
            dr["key_value"] = drData["key_value"];
            dr["status"] = "Closed";
            dr["source_title"] = drData["source_title"];
            dr["target_title"] = drData["target_title"];
            dr["is_active"] = "false";
            dr["created_by"] = HttpContext.Current.Session["user_afrowid"].ToString();
            dr["owner_id"] = HttpContext.Current.Session["user_afrowid"].ToString(); // Added by srivatsan on June 15 2009 for owner id field
            dr["modified_by"] = HttpContext.Current.Session["user_afrowid"].ToString();
            dr["created_at"] = DateTime.Now.ToString();
            dr["modified_at"] = DateTime.Now.ToString();
            dr["object_record_created_at"] = drData["object_record_created_at"]; // Added by srivatsan
            dr["object_record_created_by"] = drData["object_record_created_by"];
            dsInputInfo.Tables["p_workflow_history"].Rows.Add(dr);
            Master objMaster = new Master();
            bool bReturn = objMaster.ProcessOperation(dsPageInfo, dsInputInfo, "INSERT");
            objMaster = null;
            SR = null;
            dsInputInfo = null;
            dsPageInfo = null;
            dr = null;
            if (bReturn)
            {
                updateActiveFlag(drData["af_row_id"].ToString());
                drData = null;

                RedirectPage();
                //string sQuery = Request.UrlReferrer.Query;
                //if (sQuery.Contains("&cp="))
                //{
                //    sQuery = sQuery.Substring(0, sQuery.IndexOf("&cp=")) + "&cp=" + hihCurrentPageId.Value;
                //}
                //else
                //    sQuery = sQuery + "&cp=" + hihCurrentPageId.Value;
                //Response.Redirect(Request.UrlReferrer.LocalPath + sQuery, false);

                //Session["cp"] = hihCurrentPageId.Value;

                //Response.Redirect(Request.Url.AbsoluteUri, false);
                //Response.Redirect(Request.Url.AbsoluteUri + "&cp=" + hihCurrentPageId.Value, false);
                //Server.Transfer(Request.UrlReferrer.PathAndQuery + "&cp=" + hihCurrentPageId.Value);
            }
            drData = null;
        }
        catch (Exception Ex)
        {
            LogWriter.WriteLog("btnClose_Click : Error : " + Ex.Message);
        }
    }

    private void RedirectPage()
    {
        try
        {

            string sQuery = Request.UrlReferrer.Query;

            if (sQuery.Contains("&cp="))
            {
                sQuery = sQuery.Substring(0, sQuery.IndexOf("&cp=")) + "&cp=" + hihCurrentPageId.Value;
            }
            else
                sQuery = sQuery + "&cp=" + hihCurrentPageId.Value;

            if (sQuery.ToLower().Contains("&ddobj="))
            {
                sQuery = sQuery.Substring(0, sQuery.IndexOf("&ddobj="));
            }
            if (sQuery.ToLower().Contains("&ddcby="))
            {
                sQuery = sQuery.Substring(0, sQuery.IndexOf("&ddcby="));
            }
            if (sQuery.ToLower().Contains("&ddcat="))
            {
                sQuery = sQuery.Substring(0, sQuery.IndexOf("&ddcat="));
            }



            if (ddobject.SelectedIndex > 0)
            {
                sQuery += "&ddobj=" + ddobject.SelectedValue;
            }
            if (ddcreatedby.SelectedIndex > 0)
            {
                sQuery += "&ddcby=" + ddcreatedby.SelectedValue;
            }
            if (ddcreatedat.SelectedIndex > 0)
            {
                sQuery += "&ddcat=" + ddcreatedat.SelectedValue;
            }


            Response.Redirect(Request.UrlReferrer.LocalPath + sQuery, false);
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("RedirectPage : Error : " + Ex.Message);
        }
    }

    private void getButtons(DataRow dr, ref TableRow htr, string Status)
    {
        try
        {
            bool isRulesetPresent = true;
            if (getColumnValue("sy_workflow_ruleset", "af_row_id", dr["ruleset_id"].ToString().ToString(), "af_row_id") == "")
                isRulesetPresent = false;
            Table btTable = new Table();
            TableRow btRow = new TableRow();
            TableCell btCell = new TableCell();
            btTable.BorderStyle = BorderStyle.None;
            btTable.CellPadding = 2;
            btTable.CellSpacing = 0;
            ImageButton btnAcc = new ImageButton();
            btnAcc.ID = "App" + dr["af_row_id"].ToString();
            btnAcc.AlternateText = "Approve";
            btnAcc.ImageUrl = "Images/approve.gif";
            btnAcc.OnClientClick = "javascript:getComments('" + dr["af_row_id"].ToString() + "','Approved');disableThis('ctl00_ContentPlaceHolder3_App" + dr["af_row_id"].ToString() + "','Reset'); return false;";
            ImageButton btnRej = new ImageButton();
            btnRej.ID = "Rej" + dr["af_row_id"].ToString();
            btnRej.ImageUrl = "Images/reject.gif";
            btnRej.AlternateText = "Reject";
            btnRej.OnClientClick = "javascript:getComments('" + dr["af_row_id"].ToString() + "','Rejected');disableThis('ctl00_ContentPlaceHolder3_Rej" + dr["af_row_id"].ToString() + "','Reset'); return false;";

            if (!isRulesetPresent)
            {
                btnAcc.Enabled = false;
                btnRej.Enabled = false;
            }
            btnApproveAll.Enabled = true;
            TableCell tc = new TableCell();
            //tc.Width = Unit.Point(90);
            //tc.Controls.Add(btnAcc);
            Literal lt = new Literal();
            lt.Text = "&nbsp;";
            //tc.Controls.Add(lt);
            btCell.Controls.Add(btnAcc);
            btRow.Cells.Add(btCell);
            btCell = new TableCell();
            btCell.Controls.Add(btnRej);
            btRow.Cells.Add(btCell);
            btTable.Rows.Add(btRow);
            //htr.Cells.Add(tc);
            tc = new TableCell();
            tc.Controls.Add(btTable);
            tc.Attributes.Add("align", "right");
            htr.Cells.Add(tc);
            //tc = new TableCell();
            //tc.Controls.Add(btnRej);
            //htr.Cells.Add(tc);
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("getButtons : Error : " + Ex.Message);
        }
    }

    void btnRej_Click(object sender, ImageClickEventArgs e)
    {
        try
        {
            disableImageButtons(this.Controls);
            ImageButton btn = (ImageButton)sender;
            string sKey_Value = btn.Attributes["key"];
            bool bReturn = SaveRecord(sKey_Value, "Rejected");
            btn = null;
            if (bReturn)
            {
                RedirectPage();
            }
            //Response.Redirect(Request.Url.AbsoluteUri, false);
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("btnRej_Click : Error : " + Ex.Message);
        }
    }

    private bool fieldUpdate(string Key_Value, string TableName, string StatusCode)
    {
        bool bReturn = true;
        try
        {
            DataRow drData = getWorkFlowHistoryRecord(Key_Value);
            GetObjectTable(TableName);
            string sRule_Id = drData["rule_id"].ToString();
            if (drData["status"].ToString().ToLower() == "rejected") //if (StatusCode.ToLower() == "rejected" || drData["status"].ToString().ToLower() == "rejected")
            {
                //sRule_Id = getPreviousSubmittedRuleId(); //getPreviousOrNextRuleId(drData, -1);
                // on rejection of a work item, we have to get the appropriate work item reject field update data
                sRule_Id = getPreviousRuleId(drData, "af_row_id");
                //sRule_Id = getPreviousOrNextRuleId(drData, -1);

                //if (getColumnValue("sy_workflow_rules","af_row_id",sRule_Id,"target").ToLower() == "user_from_field")
                //sRule_Id = getPreviousSubmittedRuleId(sRule_Id);
                //else
                //sRule_Id = getPreviousSubmittedRuleId();
            }

            //string sSql = "select * from " + TableName + " where rule_id = '" + sRule_Id + "'";
            //DataRow drField = HDD.GetTableRow(sSql);
            DataRow drField = getDataRow(TableName, "rule_id", sRule_Id);
            if (drField != null)
                if (drField["field_name"].ToString() != "")
                {
                    //sSql = "select object_table from sy_obj where af_row_id='" + drData["object_id"].ToString() + "'";
                    //HDD.GetColumnValue(sSql);
                    string object_table = getColumnValue("sy_obj", "af_row_id", drData["object_id"].ToString(), "object_table");
                    string sSql = "update [" + object_table + "] set [" + drField["field_name"].ToString() + "]='" + drField["field_value"].ToString() + "' where af_row_id='" + drData["key_value"].ToString() + "'";
                    SqlCommand sqlComm = new SqlCommand(sSql);
                    int iRet = HDD.ExecuteCmdObj(sqlComm);
                    sqlComm = null;
                    string strContent = getColumnValue("sy_obj", "object_table", object_table, "object_name")/*object_table.Replace("bu_", "")*/ + "," + drField["field_name"].ToString() + "," + drField["field_value"].ToString();
                    //Added by John Sathish
                    //sSql = "select * from sy_workflow_action where rule_id = '" + sRule_Id + "'";
                    //DataTable dtWorkflowAction = HDD.GetDataTable(sSql);
                    //DataRow drWorkflow = null;
                    if (StatusCode.ToLower() == "approved")
                    {
                        DataRow[] drWorkflow = dsWorkflow.Tables["sy_workflow_action"].Select("action_event='OA' and rule_id = '" + sRule_Id + "'");
                        //"select * from sy_workflow_action where rule_id = '" + dr["af_row_id"].ToString() + "'";
                        //drWorkflow = getDataRow("sy_workflow_action", "action_event", "OA");
                        //sendEmail(drWorkflow, "", 
                        if (drWorkflow.Length > 0)
                        {
                            sendEmail(drField, strContent, drWorkflow[0]["parameter"].ToString(), object_table, drData["key_value"].ToString()); //Modified by 'Imran Gani' on 22/05/12, to send email with required data.
                        }
                    }
                    else if (StatusCode.ToLower() == "rejected")
                    {
                        //drWorkflow = getDataRow("sy_workflow_action", "action_event", "OR");
                        DataRow[] drWorkflow = dsWorkflow.Tables["sy_workflow_action"].Select("action_event='OR' and rule_id = '" + sRule_Id + "'");
                        if (drWorkflow.Length > 0)
                        {
                            sendEmail(drField, strContent, drWorkflow[0]["parameter"].ToString(), object_table, drData["key_value"].ToString()); //Modified by 'Imran Gani' on 22/05/12, to send email with required data.
                        }
                    }
                    //Up to this
                }
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("fieldUpdate : Error : " + ex.Message);
            bReturn = false;
        }
        return bReturn;
    }

    private void disableImageButtons(ControlCollection frmControls)
    {
        try
        {
            foreach (Control con in frmControls)
            {
                if (con is ImageButton)
                {
                    ImageButton img = (ImageButton)con;
                    img.Enabled = false;
                }
                else if (con.Controls.Count > 0)
                    disableImageButtons(con.Controls);
            }
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("disableImageButtons : Error : " + Ex.Message);
        }
    }

    void btnAcc_Click(object sender, ImageClickEventArgs e)
    {
        try
        {
            disableImageButtons(this.Controls);
            ImageButton btn = (ImageButton)sender;
            string sKey_Value = btn.Attributes["key"];
            bool bReturn = SaveRecord(sKey_Value, "Approved");
            btn = null;
            if (bReturn)
            {
                RedirectPage();
            }
            //Response.Redirect(Request.Url.AbsoluteUri, false);
        }
        catch (Exception Ex)
        {
            LogWriter.WriteLog("btnAcc_Click : Error : " + Ex.Message);
        }
    }

    private string checkUserTitle(ref DataRow drData, string StatusCode)
    {
        string sCurrentUserTitle = getColumnValue("sy_usr", "af_row_id", Session["user_afrowid"].ToString(), "title");
        if (drData["target_title"].ToString() != sCurrentUserTitle)
            StatusCode = "Approved and Submitted";
        return StatusCode;
    }

    private bool SaveRecord(string Key_Value, string StatusCode)
    {
        bool bReturn = false;
        try
        {
            dsInputInfo = new DataSet();
            string sRule_id = "";
            DataRow drData = getWorkFlowHistoryRecord(Key_Value);//getting the record from sy_workflow_history
            if (drData["status"].ToString().ToLower() == "rejected") // if rejected the previous rule id should be found to create a rejected workflow for the user who submitted the work item
                sRule_id = getPreviousRuleId(drData, "af_row_id");
            else
                sRule_id = drData["rule_id"].ToString();
            DataSet dsPageInfo = null;
            getPageInfo(ref dsPageInfo);
            //DataSet dsInputInfo = new DataSet();
            ScreenReader SR = new ScreenReader();
            SR.CreatePageDataTable(dsInputInfo, dsPageInfo.Tables["page"], dsPageInfo.Tables["pagectrl"]);
            DataRow dr = dsInputInfo.Tables["p_workflow_history"].NewRow();
            sWorkflowHistory_ref = drData["workflow_history_ref"].ToString();
            dr["af_row_id"] = SR.getHashKey(); ;
            dr["ruleset_id"] = drData["ruleset_id"];
            dr["rule_id"] = sRule_id;
            dr["object_id"] = drData["object_id"];
            dr["pageset_id"] = drData["pageset_id"];
            dr["menu_id"] = drData["menu_id"];
            dr["key_value"] = drData["key_value"];
            dr["status"] = StatusCode;
            dr["source_title"] = drData["source_title"];
            dr["target_title"] = drData["target_title"];
            dr["comments"] = hfComments.Value;
            dr["workflow_history_ref"] = drData["workflow_history_ref"];
            dr["created_by"] = HttpContext.Current.Session["user_afrowid"].ToString();
            dr["owner_id"] = HttpContext.Current.Session["user_afrowid"].ToString();// Added by srivatsan on June 15 2009 for owner id field
            dr["modified_by"] = HttpContext.Current.Session["user_afrowid"].ToString();
            dr["created_at"] = DateTime.Now.ToString();
            dr["modified_at"] = DateTime.Now.ToString();
            dr["object_record_created_at"] = drData["object_record_created_at"]; // Added by srivatsan
            dr["object_record_created_by"] = drData["object_record_created_by"];


            dsInputInfo.Tables["p_workflow_history"].Rows.Add(dr);
            if (StatusCode == "Approved")
            {
                dr["is_active"] = "false";
                checkForNextRule(drData, dr, ref dsInputInfo);
            }
            else if (StatusCode == "Rejected")
            {
                checkForPreviousRule(drData, dr, ref dsInputInfo);
                dr["is_active"] = "true";
            }

            //if (deleteWorkItemForUser(Key_Value) == 1)
            //{
            Master objMaster = new Master();
            bReturn = objMaster.ProcessOperation(dsPageInfo, dsInputInfo, "INSERT");

            if (bReturn)
            {
                updateActiveFlag(drData["af_row_id"].ToString()); //update the work item active flag
                if (StatusCode.ToLower() == "approved")
                    bReturn = fieldUpdate(Key_Value, "sy_field_updates", StatusCode); // field update for approved work item
                else if (StatusCode.ToLower() == "rejected")
                    bReturn = fieldUpdate(Key_Value, "sy_reject_field_updates", StatusCode); // field update for rejected work item

                //if (bReturn)
                //    Response.Redirect(Request.Url.AbsoluteUri, false);
            }
            objMaster = null;
            //}
            SR = null;
            dsInputInfo = null;
            dsPageInfo = null;
            drData = null;
            dr = null;
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("SaveRecord : Error : " + ex.Message);
        }
        return bReturn;
    }

    private void updateActiveFlag(string s_AF_ROW_ID)
    {
        try
        {
            string sSql = "update sy_workflow_history set is_active='0' where af_row_id='" + s_AF_ROW_ID + "'";
            SqlCommand sqlComm = new SqlCommand(sSql);
            int iRet = HDD.ExecuteCmdObj(sqlComm);
            sSql = "delete from sy_workitem_user where af_row_id='" + s_AF_ROW_ID + "'";
            sqlComm = new SqlCommand(sSql);
            iRet = HDD.ExecuteCmdObj(sqlComm);
            sqlComm = null;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("updateActiveFlag : Error : " + ex.Message);
        }
    }

    private DataRow getWorkFlowHistoryRecord(string Key_Value)
    {
        DataRow drReturn = null;
        try
        {
            DataRow[] drr = dsWorkflow.Tables["sy_workflow_history"].Select("af_row_id='" + Key_Value + "'");

            if (drr.Length > 0)
            {
                drReturn = drr[0];
            }
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("getWorkFlowHistoryRecord : Error : " + ex.Message);
        }
        return drReturn;
    }


    private bool checkWorkflowForUser(DataRow dr)
    {
        bool bReturn = false;
        try
        {
            HDDatabase HDD = new HDDatabase();
            string sBased_On = getColumnValue("sy_workflow_ruleset", "af_row_id", dr["ruleset_id"].ToString(), "based_on").ToLower();

            if (Session["userid_afrowid"].ToString() == "650a333596bbf348" && sBased_On == "")
                return true;

            if (sBased_On == "title")
            {
                string sCurrentUserTitle = getColumnValue("sy_usr", "af_row_id", Session["user_afrowid"].ToString(), "title");
                sCurrentUserTitle = getColumnValue("sy_org_structure", "af_row_id", sCurrentUserTitle, "title");
                string sCurrentUserId = getColumnValue("sy_usr", "af_row_id", Session["user_afrowid"].ToString(), "user_id");
                string sCurrentUserIdReportingTo = getColumnValue("sy_usr", "af_row_id", Session["user_afrowid"].ToString(), "reporting_to");
                string sRowUserReporting = dr["target_title"].ToString();

                if (sCurrentUserTitle.ToLower() == sRowUserReporting.ToLower() && (dr["status"].ToString().ToLower() == "submitted"))
                {
                    if (dsWorkflow.Tables["sy_usr"].Select("af_row_id='" + dr["created_by"].ToString() + "' and reporting_to='" + Session["user_afrowid"].ToString() + "'").Length > 0)
                        bReturn = true;
                }
                else if ((dr["status"].ToString().ToLower() == "rejected") && (dr["source_title"].ToString().ToLower() == sCurrentUserTitle.ToLower() && dr["target_title"].ToString().ToLower() == sRowUserReporting.ToLower() && dr["created_by"].ToString() == sCurrentUserIdReportingTo))
                    bReturn = true;
            }
            else if (sBased_On == "user")
            {
                if (dr["target_title"].ToString().ToLower() == HttpContext.Current.Session["userid"].ToString().ToLower() && dr["status"].ToString().ToLower() != "rejected")
                    bReturn = true;
                else if (dr["source_title"].ToString().ToLower() == HttpContext.Current.Session["userid"].ToString().ToLower() && dr["status"].ToString().ToLower() == "rejected")
                    bReturn = true;
            }
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("checkWorkflowForUser : Error : " + Ex.Message);
        }
        return bReturn;
    }

    private string getColumnValue(string Tablename, string whereFieldname, string Value, string Fieldname)
    {
        string sValue = "";
        try
        {
            if (dsWorkflow.Tables.Contains(Tablename))
            {
                DataRow[] drr = dsWorkflow.Tables[Tablename].Select(whereFieldname + "='" + Value.Replace("'", "''") + "'");
                if (drr.Length > 0)
                    sValue = drr[0][Fieldname].ToString();
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("getColumnValue : Error :" + ex.Message);
        }
        return sValue;
    }

    private DataRow getDataRow(string Tablename, string whereFieldname, string Value)
    {
        DataRow dr = null;
        try
        {
            if (dsWorkflow.Tables.Contains(Tablename))
            {
                DataRow[] drr = dsWorkflow.Tables[Tablename].Select(whereFieldname + "='" + Value.Replace("'", "''") + "'");
                if (drr.Length > 0)
                    dr = drr[0];
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("getColumnValue : Error :" + ex.Message);
        }
        return dr;
    }

    //called after getting the comments from the user
    protected void btnSaveComments_Click(object sender, EventArgs e)
    {
        try
        {
            string sKey_Value = hf_af_row_id.Value;
            bool bReturn = SaveRecord(sKey_Value, hf_Status.Value);
            hf_af_row_id.Value = "";
            if (bReturn)
            {
                RedirectPage();
            }
            //Response.Redirect(Request.Url.AbsoluteUri, false);
        }
        catch (Exception Ex)
        {
            LogWriter.WriteLog("btnSaveComments_Click : Error : " + Ex.Message);
        }
    }

    private string getEditPage(string MenuId)
    {
        string sRet = "DynamicScreen.aspx";

        string sMenuURLType = getColumnValue("sy_mnu", "af_row_id", MenuId, "pageset_url_type");
        if (sMenuURLType == "2")
            sRet = getColumnValue("sy_mnu", "af_row_id", MenuId, "pageset_url");
        return sRet;
    }

    private void checkForNextRule(DataRow drRule, DataRow drData, ref DataSet dsInputInfo)
    {
        try
        {
            int iSeq = int.Parse(getColumnValue("sy_workflow_rules", "af_row_id", drRule["rule_id"].ToString(), "sequence"));//finding the rule number
            string sCurrentUserTitle = getColumnValue("sy_usr", "af_row_id", Session["user_afrowid"].ToString(), "title");
            //sCurrentUserTitle = getColumnValue("sy_org_structure", "af_row_id", sCurrentUserTitle, "title");
            string sReportingTo = getColumnValue("sy_usr", "af_row_id", Session["user_afrowid"].ToString(), "reporting_to");
            string sReportingToTitle = getColumnValue("sy_usr", "af_row_id", sReportingTo, "title");
            //sReportingToTitle = getColumnValue("sy_org_structure", "af_row_id", sReportingToTitle, "title");

            DataRow[] drr = null;// dsWorkflow.Tables["sy_workflow_rules"].Select("ruleset='" + drRule["ruleset_id"].ToString() + "' and sequence > 0 and source_title = '" + sCurrentUserTitle + "' and target_title = '" + sReportingToTitle + "'", "sequence asc");
            drr = dsWorkflow.Tables["sy_workflow_rules"].Select("ruleset='" + drRule["ruleset_id"].ToString() + "'");
            int iMaxSeq = drr.Length;
            //int iSeq = int.Parse(getColumnValue("sy_workflow_rules", "af_row_id", drRule["rule_id"].ToString(), "sequence"));
            drr = null;
            //if (drRule["status"].ToString().ToLower() == "rejected") iSeq = iSeq - 1;
            bool bLastRule = false;
            if (drRule["status"].ToString().ToLower() == "rejected" && iSeq == iMaxSeq - 1) // if the user accepts the last rule then it should be closed but if its a rejected work item it has to be taken to the next level
            {
                bLastRule = true;
                iSeq = iSeq - 1;
            }
            //if (drRule["rule_id"].ToString() == drData["rule_id"].ToString() && drRule["status"].ToString().ToLower() == "rejected") iSeq = iSeq - 1;
            if (iSeq < iMaxSeq - 1)
            {
                if (drRule["status"].ToString().ToLower() != "rejected") iSeq = iSeq + 1;
                if (bLastRule) iSeq = iSeq + 1;
                //if (drRule["rule_id"].ToString() == drData["rule_id"].ToString() && drRule["status"].ToString().ToLower() == "rejected") iSeq = iSeq + 1;

                //if (drRule["rule_id"].ToString() == drData["rule_id"].ToString() && drRule["status"].ToString().ToLower() == "rejected") iSeq = iSeq + 1;

                string sBased_On = getColumnValue("sy_workflow_ruleset", "af_row_id", drRule["ruleset_id"].ToString(), "based_on").ToLower();
                if (sBased_On == "user")
                {
                    //drr = dsWorkflow.Tables["sy_workflow_rules"].Select("ruleset='" + drRule["ruleset_id"].ToString() + "'");
                    //int iMaxSeq = drr.Length;
                    //int iSeq = int.Parse(getColumnValue("sy_workflow_rules", "af_row_id", drRule["rule_id"].ToString(), "sequence"));
                    //if (iSeq < iMaxSeq - 1) iSeq = iSeq + 1;
                    drr = dsWorkflow.Tables["sy_workflow_rules"].Select("ruleset='" + drRule["ruleset_id"].ToString() + "' and sequence = '" + iSeq.ToString() + "'");
                }
                else
                {
                    //string sSkip_Rule = getColumnValue("sy_workflow_ruleset", "af_row_id", drRule["ruleset_id"].ToString(), "skip_title").ToLower();
                    //if (sSkip_Rule == "true")
                    drr = dsWorkflow.Tables["sy_workflow_rules"].Select("ruleset='" + drRule["ruleset_id"].ToString() + "' and sequence='" + iSeq.ToString() + "'");
                    //else if (sSkip_Rule == "false" || sSkip_Rule == "")
                    //drr = dsWorkflow.Tables["sy_workflow_rules"].Select("ruleset='" + drRule["ruleset_id"].ToString() + "' and source_title='" + sCurrentUserTitle + "' and target_title='" + sReportingToTitle + "'");
                }

                if (drr.Length > 0)
                {
                    foreach (DataRow dr in drr)
                    {
                        submitNewRule(ref dsInputInfo, drData, drr[0]);
                    }
                }
            }
            else
            {
                //DataRow[] drMaxRule = dsWorkflow.Tables["sy_workflow_rules"].Select("ruleset='" + drRule["ruleset_id"].ToString() + "'", "sequence desc");
                //if (drMaxRule[0]["af_row_id"].ToString() == drRule["rule_id"].ToString())
                dsInputInfo.Tables["p_workflow_history"].Rows[dsInputInfo.Tables["p_workflow_history"].Rows.Count - 1]["status"] = "Completed";
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("checkForNextRule : Error :" + ex.Message);
        }
    }

    private void checkForPreviousRule(DataRow drRule, DataRow drData, ref DataSet dsInputInfo)
    {
        try
        {
            string sCurrentUserTitle = getColumnValue("sy_usr", "af_row_id", Session["user_afrowid"].ToString(), "title");
            //sCurrentUserTitle = getColumnValue("sy_org_structure", "af_row_id", sCurrentUserTitle, "title");
            string sReportingTo = getColumnValue("sy_usr", "af_row_id", Session["user_afrowid"].ToString(), "reporting_to");

            string sSequence = getColumnValue("sy_workflow_rules", "af_row_id", drRule["rule_id"].ToString(), "sequence");
            int iSeq = int.Parse(sSequence);

            string sBased_On = getColumnValue("sy_workflow_rules", "af_row_id", drRule["rule_id"].ToString(), "target"); // Added by 'Imran Gani' on 27-Mar-2013.
            //string sBased_On = getColumnValue("sy_workflow_ruleset", "af_row_id", drRule["ruleset_id"].ToString(), "based_on").ToLower();
            if (sBased_On == "title" || sBased_On == "")
            {
                if (drData["target_title"].ToString().ToLower() != sCurrentUserTitle.ToLower())
                {
                    if (iSeq > 0) iSeq = iSeq - 1;

                    DataRow[] drr = dsWorkflow.Tables["sy_workflow_rules"].Select("ruleset='" + drRule["ruleset_id"].ToString() + "' and sequence = '" + iSeq + "'");

                    foreach (DataRow dr in drr)
                    {
                        drData["rule_id"] = dr["af_row_id"];
                        drData["source_title"] = dr["source_title"];
                        drData["target_title"] = dr["target_title"];
                    }
                }
            }
            else if (sBased_On == "user")
            {
                if (drData["status"].ToString().ToLower() == "rejected" && drData["rule_id"].ToString() != drRule["rule_id"].ToString())
                {
                    if (drData["target_title"].ToString().ToLower() != Session["userid"].ToString().ToLower())
                    {
                        if (iSeq > 0) iSeq = iSeq - 1;

                        DataRow[] drr = dsWorkflow.Tables["sy_workflow_rules"].Select("ruleset='" + drRule["ruleset_id"].ToString() + "' and sequence = '" + iSeq + "'");

                        foreach (DataRow dr in drr)
                        {
                            drData["rule_id"] = dr["af_row_id"];
                            drData["source_title"] = dr["source_title"];
                            drData["target_title"] = dr["target_title"];
                        }
                        drr = null;
                    }
                }
            }
            addWorkitemForUser(dsInputInfo, drData, "rejected");
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("checkForPreviousRule : Error :" + ex.Message);
        }
    }

    private void submitNewRule(ref DataSet dsInputInfo, DataRow drData, DataRow drNewRule)
    {
        try
        {
            ScreenReader SR = new ScreenReader();
            DataRow dr = dsInputInfo.Tables["p_workflow_history"].NewRow();
            dr["af_row_id"] = SR.getHashKey();
            dr["ruleset_id"] = drData["ruleset_id"];
            dr["rule_id"] = drNewRule["af_row_id"];
            dr["object_id"] = drData["object_id"];
            dr["pageset_id"] = drData["pageset_id"];
            dr["menu_id"] = drData["menu_id"];
            dr["key_value"] = drData["key_value"];
            dr["status"] = "Submitted";
            dr["source_title"] = drNewRule["source_title"];
            dr["target_title"] = drNewRule["target_title"];
            dr["comments"] = hfComments.Value;
            dr["workflow_history_ref"] = sWorkflowHistory_ref;
            dr["is_active"] = "true";
            dr["created_by"] = HttpContext.Current.Session["user_afrowid"].ToString();
            dr["owner_id"] = HttpContext.Current.Session["user_afrowid"].ToString();// Added by srivatsan on June 15 2009 for owner id field
            dr["modified_by"] = HttpContext.Current.Session["user_afrowid"].ToString();
            dr["created_at"] = DateTime.Now.AddSeconds(1).ToString();
            dr["modified_at"] = DateTime.Now.AddSeconds(1).ToString();
            dr["object_record_created_at"] = drData["object_record_created_at"]; // Added by srivatsan
            dr["object_record_created_by"] = drData["object_record_created_by"];
            dsInputInfo.Tables["p_workflow_history"].Rows.Add(dr);
            addWorkitemForUser(dsInputInfo, dr, "approved");
            SR = null;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("submitNewRule : Error :" + ex.Message);
        }
    }

    private int deleteWorkItemForUser(string Ruleset_Id, string Rule_Id, string Key_Value, string For_User)
    {
        int iRet = 0;
        try
        {
            string sSql = "delete sy_workitem_user where ruleset_id='" + Ruleset_Id + "' and rule_id='" + Rule_Id + "' and key_value='" + Key_Value + "' and is_active='1'";
            SqlCommand sqlComm = new SqlCommand(sSql);
            iRet = HDD.ExecuteCmdObj(sqlComm);
            sqlComm = null;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("deleteWorkItemForUser : Error :" + ex.Message);
        }
        return iRet;
    }

    private int deleteWorkItemForUser(string AF_ROW_ID)
    {
        int iRet = 0;
        try
        {
            string sSql = "delete sy_workitem_user where af_row_id='" + AF_ROW_ID + "'";
            SqlCommand sqlComm = new SqlCommand(sSql);
            iRet = HDD.ExecuteCmdObj(sqlComm);
            sqlComm = null;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("deleteWorkItemForUser : Error :" + ex.Message);
        }
        return iRet;
    }

    private void addWorkitemForUser(DataSet dsInputInfo, DataRow drHistory, string Status)
    {
        try
        {
            DataRow dr = dsInputInfo.Tables["p_workitem_user"].NewRow();
            dr["af_row_id"] = drHistory["af_row_id"];
            dr["ruleset_id"] = drHistory["ruleset_id"];
            dr["rule_id"] = drHistory["rule_id"];
            dr["object_id"] = drHistory["object_id"];
            dr["pageset_id"] = drHistory["pageset_id"];
            dr["menu_id"] = drHistory["menu_id"];
            dr["key_value"] = drHistory["key_value"];
            dr["status"] = drHistory["status"];
            dr["source_title"] = drHistory["source_title"];
            dr["target_title"] = drHistory["target_title"];
            dr["comments"] = drHistory["comments"];
            dr["created_by"] = drHistory["created_by"];
            dr["owner_id"] = drHistory["owner_id"];// Added by srivatsan on June 15 2009 for owner id field
            dr["modified_by"] = drHistory["modified_by"];
            dr["created_at"] = drHistory["created_at"];
            dr["modified_at"] = drHistory["modified_at"];
            dr["for_user"] = getWorkitem_User(ref drHistory, Status);
            dr["is_active"] = "true";
            dr["object_record_created_at"] = drHistory["object_record_created_at"]; // Added by srivatsan
            dr["object_record_created_by"] = drHistory["object_record_created_by"];
            if (dr["for_user"].ToString() != Session["user_afrowid"].ToString())
            {
                dsInputInfo.Tables["p_workitem_user"].Rows.Add(dr);
                //deleteWorkItemForUser(dr["ruleset_id"].ToString(), drHistory.Table.Rows[0]["rule_id"].ToString(), dr["key_value"].ToString(), dr["for_user"].ToString());
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("addWorkitemForUser : Error :" + ex.Message);
        }
    }

    private string getWorkitem_User(ref DataRow drHistory, string Status)
    {
        //finding the appropriate user for the work item considering all the criteria like status, based on
        string sUser = "";
        try
        {
            string sBased_On = getColumnValue("sy_workflow_ruleset", "af_row_id", drHistory["ruleset_id"].ToString(), "based_on").ToLower();
            string sTarget = getColumnValue("sy_workflow_rules", "af_row_id", drHistory["rule_id"].ToString(), "Target").ToLower();

            if (Status == "approved")
            {
                if (sTarget == "user")
                {
                    sUser = drHistory["target_title"].ToString();
                }
                else if (sTarget == "title")
                {
                    //string sRule_Id = getPreviousOrNextRuleId(drHistory, 1);
                    //string sSequence = getColumnValue("sy_workflow_rules", "af_row_id", drHistory["rule_id"].ToString(), "sequence");
                    //int iSeq = int.Parse(sSequence);
                    sUser = getWorkitemUser(dsWorkflow.Tables["sy_workflow_rules"].Select("af_row_id = '" + drHistory["rule_id"].ToString() + "'")[0], ref drHistory);
                    /*string sCurrentUserTitle = getColumnValue("sy_usr", "af_row_id", Session["user_afrowid"].ToString(), "title");
                    //sCurrentUserTitle = getColumnValue("sy_org_structure", "af_row_id", sCurrentUserTitle, "title");
                    string sReportingTo = getColumnValue("sy_usr", "af_row_id", Session["user_afrowid"].ToString(), "reporting_to");
                    string sReportingToTitle = getColumnValue("sy_usr", "af_row_id", sReportingTo, "title");
                    //sReportingToTitle = getColumnValue("sy_org_structure", "af_row_id", sReportingToTitle, "title");
                    if (drHistory["source_title"].ToString().ToLower() == sCurrentUserTitle.ToLower() && drHistory["target_title"].ToString().ToLower() == sReportingToTitle.ToLower())
                        sUser = sReportingTo;*/
                }
                else if (sTarget == "user_from_field")
                {
                    sUser = getUserFromData(dsWorkflow.Tables["sy_workflow_rules"].Select("af_row_id = '" + drHistory["rule_id"].ToString() + "'")[0]);
                }
            }
            else if (Status == "rejected")
            {
                if (sBased_On == "user")
                {
                    sUser = drHistory["source_title"].ToString();
                }
                else if (sBased_On == "title")
                {
                    #region MyRegion
                    //string sWorkItem = getColumnValue("sy_workflow_history", "af_row_id", hf_af_row_id.Value, "workflow_history_ref");
                    //sWorkItem = getColumnValue("sy_workflow_history", "af_row_id", hf_af_row_id.Value, "rule_id");
                    //if (sWorkItem != "")
                    //{
                    //    DataRow[] drr = dsWorkflow.Tables["sy_workflow_rules"].Select("ruleset='" + drHistory["ruleset_id"].ToString() + "'");
                    //    int iMaxSeq = drr.Length;
                    //    string sSequence = getColumnValue("sy_workflow_rules", "af_row_id", drHistory["rule_id"].ToString(), "sequence");
                    //    int iSeq = int.Parse(sSequence);
                    //    if (iSeq > 0 && iSeq < iMaxSeq - 1)
                    //        sWorkItem = getColumnValue("sy_workflow_history", "af_row_id", sWorkItem, "workflow_history_ref");
                    //    sUser = getColumnValue("sy_workflow_history", "af_row_id", sWorkItem, "created_by");

                    //    /*string sRule_id = getColumnValue("sy_workflow_history", "af_row_id", sWorkItem, "rule_id");

                    //    string sUserTitle = getColumnValue("sy_usr", "af_row_id", sWorkItemuser, "title");
                    //    sUserTitle = getColumnValue("sy_org_structure", "af_row_id", sUserTitle, "title");
                    //    string sReportingTo = getColumnValue("sy_usr", "af_row_id", sWorkItemuser, "reporting_to");
                    //    string sReportingToTitle = getColumnValue("sy_usr", "af_row_id", sReportingTo, "title");
                    //    sReportingToTitle = getColumnValue("sy_org_structure", "af_row_id", sReportingToTitle, "title");
                    //    string sSource_Title = getColumnValue("sy_workflow_rules", "af_row_id", sRule_id, "source_title");
                    //    string sTarget_Title = getColumnValue("sy_workflow_rules", "af_row_id", sRule_id, "target_title");
                    //    if (sSource_Title.ToLower() == sUserTitle.ToLower() && sTarget_Title.ToLower() == sReportingToTitle.ToLower())
                    //        sUser = sReportingTo;*/
                    //}
                    //else
                    //    sUser = getColumnValue( "workitem", "af_row_id", hf_af_row_id.Value, "created_by");
                    #endregion

                    sUser = getWorkflowRejectUser(sWorkflowHistory_ref, drHistory["rule_id"].ToString());
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("getWorkitem_User : Error :" + ex.Message);
        }
        return sUser;
    }

    #region MyRegion
    private void displayHistory()
    {
        //not used
        try
        {
            Literal liHistory = new Literal();
            liHistory.ID = "His";
            liHistory.Text = @"<div id=""div_his"" style=""display: none; padding: 10px 6px 10px 6px; z-index: 2;width: 600px; position: absolute; top: 97px; left: 249px; filter: alpha(opacity=100);opacity: 1; height: 138px;"" class=""WizardDiv"">"
                + @"<table width=""100%"" border=""0"" cellspacing=""0"" cellpadding=""0"">" + getTableContent().ToString() + @"<tr class=""displaygridbottom""><td colspan=""7"">&nbsp;</td></tr></table>"
                + @"<table width=""100%"" border=""0"" cellspacing=""0"" cellpadding=""0"" align=""right""><tr><td colspan=""7"" align=""right""><input id=""Ok"" type=""button"" value=""  Ok  "" OnClick=""closeHistory('div_his');""/>&nbsp</td></tc></table></div>";
            Form.Controls.Add(liHistory);
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("displayHistory : Error :" + ex.Message);
        }
    }

    private StringBuilder getTableContent()
    {
        StringBuilder sbContent = new StringBuilder();

        try
        {
            sbContent.Append("<tr width='100%' class='Grid_Header'><td>Ruleset</td><td>Rule</td><td>Object</td><td>Status</td><td>Comments</td><td>Created By</td><td>created On</td></tr>");

            for (int iRowCount = 0; iRowCount < dsWorkflow.Tables["history"].Rows.Count; iRowCount++)
                getRowContent(ref sbContent, dsWorkflow.Tables["history"].Rows[iRowCount], iRowCount % 2);
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("getTableContent : Error :" + ex.Message);
        }
        return sbContent;
    }

    private void getRowContent(ref StringBuilder sbTableContent, DataRow dr, int iClass)
    {
        try
        {
            sbTableContent.Append(@"<tr width='100%' class=""Grid_Row").Append(iClass.ToString()).Append(@""">");
            sbTableContent.Append("<td>").Append(getColumnValue("sy_workflow_ruleset", "af_row_id", dr["ruleset_id"].ToString(), "name")).Append("</td>");
            sbTableContent.Append("<td>").Append(getColumnValue("sy_workflow_rules", "af_row_id", dr["rule_id"].ToString(), "name")).Append("</td>");
            sbTableContent.Append("<td>").Append(getColumnValue("sy_obj", "af_row_id", dr["object_id"].ToString(), "object_name")).Append("</td>");
            sbTableContent.Append("<td>").Append(dr["status"].ToString()).Append("</td>");
            sbTableContent.Append("<td>").Append(dr["comments"].ToString()).Append("</td>");
            sbTableContent.Append("<td>").Append(getColumnValue("sy_usr", "af_row_id", dr["created_by"].ToString(), "user_id")).Append("</td>");
            sbTableContent.Append("<td>").Append(dr["created_at"].ToString()).Append("</td></tr>");
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("getRowContent : Error :" + ex.Message);
        }
    }
    #endregion

    private void getTableFromArray(ref DataTable dtHistory, DataRow[] drr)
    {
        try
        {
            foreach (DataRow dr in drr)
                dtHistory.ImportRow(dr);
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("getTableFromArray : Error :" + ex.Message);
        }
    }
    private bool _checkInArrayList(ArrayList al, string inp)
    {
        if (al == null) return true;
        for (int i = 0; i < al.Count; i++)
        {
            if (al[i].ToString().ToLower() == inp.ToLower())
                return true;
        }
        return false;
    }
    private void loadWorkItemObjectRecords(DataSet ds, string tableName)//by sanal on dec 18, 2008
    {
        if (ds == null) return;
        if (!ds.Tables.Contains(tableName)) return;
        ArrayList alObjects = new ArrayList();
        foreach (DataRow dr in ds.Tables[tableName].Rows)
        {
            if (!_checkInArrayList(alObjects, dr["object_id"].ToString()))
                alObjects.Add(dr["object_id"].ToString());
        }
        StringBuilder temp = new StringBuilder(); string sql = ""; string objectTableName = "";
        for (int i = 0; i < alObjects.Count; i++)
        {
            temp = new StringBuilder(); sql = "";
            DataRow[] drs = ds.Tables[tableName].Select("object_id='" + alObjects[i].ToString() + "'");
            for (int j = 0; j < drs.Length; j++)
            {
                if (drs[j]["key_value"].ToString() != "")  // Added by srivatsan on June 25 2009
                {
                    if (!temp.ToString().Contains(drs[j]["key_value"].ToString()))
                    {
                        temp.Append("'" + drs[j]["key_value"].ToString() + "',");
                    }
                }
            }
            objectTableName = ds.Tables["sy_obj"].Select("af_row_id='" + alObjects[i].ToString() + "'")[0]["object_table"].ToString();
            sql = "select * from [" + objectTableName + "]" + ((temp.ToString().TrimEnd(',').Length > 0) ? (" where " + "af_row_id" + " IN (" + temp.ToString().TrimEnd(',') + ")") : "");
            if (!dsWorkflow.Tables.Contains(objectTableName))
            {
                dsWorkflow.Tables.Add(HDD.GetDataTable(sql, objectTableName).Copy());
            }
        }
    }

    #region MyRegion
    //private DataTable GetDataTable(string strSql, int stCount, string strTargetTableName,ref HDDatabase hd)
    //{
    //    DataSet dseObj = new DataSet();
    //    //string reqPath = "";//ssks
    //    try
    //    {
    //        //reqPath = HttpContext.Current.Request.Path;//ssks
    //        LogWriter.WriteLog(strSql);
    //        SqlCommand cmdObj = new SqlCommand(strSql, hd.GetConnection());
    //        SqlDataAdapter sdaObj = new SqlDataAdapter(cmdObj);
    //        sdaObj.Fill(dseObj, stCount, batchSize, "myTable");
    //        //startCount = stCount + batchSize;
    //    }
    //    catch (SqlException ex)
    //    {
    //        //strErrMessage = ex.Message;
    //        LogWriter.WriteLog(ex.Message);
    //        //if (ex.Number == 18456)
    //            //HttpContext.Current.Response.Redirect("Error.aspx?msg=" + ex.Message);
    //    }
    //    catch (Exception ex)
    //    {
    //        //strErrMessage = ex.Message;
    //        //if (reqPath.ToLower().IndexOf("default.aspx") > -1 || reqPath.ToLower().IndexOf("welcome.aspx") > -1) fillDataOnError(ref dseObj, ex.Message, this.strConString, strSql);//ssks
    //        LogWriter.WriteLog(ex.Message);
    //        //HttpContext.Current.Response.Redirect("Error.aspx");
    //    }
    //    //return dseObj;
    //    DataTable dt = new DataTable();
    //    if (dseObj.Tables.Count > 0)
    //    {
    //        if (strTargetTableName != "")
    //        {
    //            dseObj.Tables[0].TableName = strTargetTableName;
    //        }
    //        dt = dseObj.Tables[0];
    //    }
    //    return dt;

    //} 

    //private void addWorkitemForUserTable(ref DataSet ds,ref HDDatabase hd)
    //{
    //    int count = (Convert.ToInt32(hihCurrentPageId.Value)) * batchSize;
    //    DataTable dt = GetDataTable("Select * from sy_workitem_user", count, "sy_workitem_user", ref hd);
    //    ds.Tables.Add(dt.Copy());
    //}
    #endregion

    private void addWorkitemForUserTable(ref DataSet ds, string tableName)
    {

        if (hihCurrentPageId.Value == "F")
        {
            hihCurrentPageId.Value = "0";
        }
        else if (hihCurrentPageId.Value == "L")
        {
            int iTotPages = dsWorkflow.Tables[tableName].Rows.Count / GlobalValues.intNoOfRecordsPerPage;
            hihCurrentPageId.Value = iTotPages.ToString();
        }

        // Added by srivatsan

        if (hihCurrentPageId.Value == "-1")
        {
            hihCurrentPageId.Value = "0";
        }

        //System.Collections.Specialized.NameValueCollection nvc = HttpUtility.ParseQueryString(HttpContext.Current.Request.UrlReferrer.Query);
        //string cpagevalue = nvc["cp"];

        //if (cpagevalue != null)
        //{
        //    if (cpagevalue != string.Empty)
        //    {
        //        hihCurrentPageId.Value = cpagevalue;
        //    }
        //}
        // end of srivatsan


        //if (recordcount < batchSize)
        //{
        //    hihCurrentPageId.Value = "0";
        //}

        //if (ddobject.SelectedValue != "--All--" || ddcreatedby.SelectedValue != "--All--" || ddcreatedat.SelectedValue != "--All--")
        //{
        //    hihCurrentPageId.Value = "0";
        //}



        int count = (Convert.ToInt32(hihCurrentPageId.Value)) * batchSize;
        int iEnd = count + GlobalValues.intNoOfRecordsPerPage;

        int recordcount = dsWorkflow.Tables[tableName].Rows.Count;

        DataTable dtNew = ds.Tables[tableName].Clone();
        //bool noMoreData = false;
        setPageRecordInfo(ref count, dsWorkflow.Tables[tableName].Rows.Count);
        for (int i = count; i < (count + batchSize); i++)
        {
            if (i < ds.Tables[tableName].Rows.Count)
                dtNew.ImportRow(ds.Tables[tableName].Rows[i]);
            else
            {
                if (dtNew.Rows.Count == 0)
                {
                    hihCurrentPageId.Value = (Convert.ToInt32(hihCurrentPageId.Value) - 1).ToString();
                    count = (Convert.ToInt32(hihCurrentPageId.Value)) * batchSize;
                    dtNew = ds.Tables[tableName].Clone();
                    for (int j = count; j < (count + batchSize); j++)
                    {
                        if (j < ds.Tables[tableName].Rows.Count)
                            dtNew.ImportRow(ds.Tables[tableName].Rows[j]);
                    }
                    break;
                }
            }
        }
        dtNew.TableName = tableName;
        ds.Tables.Remove(ds.Tables[tableName]);
        ds.Tables.Add(dtNew);
    }

    private void setPageRecordInfo(ref int Count, int TotalCount)
    {
        if (TotalCount == 0 || TotalCount <= GlobalValues.intNoOfRecordsPerPage)
        {
            btnFirst.Visible = false;
            btnPrev.Visible = false;
            btnNext.Visible = false;
            btnLast.Visible = false;
            lblPageNoInfo.Visible = false;
            if (TotalCount == 0)
            {
                lblRecordNoInfo.Visible = false;
                return;
            }
        }
        else
        {
            btnFirst.Visible = true;
            btnPrev.Visible = true;
            btnNext.Visible = true;
            btnLast.Visible = true;
            lblPageNoInfo.Visible = true;
        }
        lblRecordNoInfo.Visible = true;
        int iEndRec = 0;

        //if (GlobalValues.intNoOfRecordsPerPage > TotalCount)
        //{
        //    iEndRec = TotalCount;
        //}
        //else if(GlobalValues.intNoOfRecordsPerPage<TotalCount)
        //{
        //    iEndRec = GlobalValues.intNoOfRecordsPerPage;
        //}
        iEndRec = Count + GlobalValues.intNoOfRecordsPerPage;
        if (iEndRec > TotalCount)
        {
            iEndRec = TotalCount;
            //Count = 0;
        }

        if (Count == TotalCount)
        {
            lblRecordNoInfo.Text = "Showing record(s) " + (Count - GlobalValues.intNoOfRecordsPerPage) + " to " + iEndRec.ToString() + " of " + TotalCount.ToString();
        }
        else
        {
            lblRecordNoInfo.Text = "Showing record(s) " + (Count + 1) + " to " + iEndRec.ToString() + " of " + TotalCount.ToString();
        }



        int intLastPage = TotalCount / GlobalValues.intNoOfRecordsPerPage;

        if (TotalCount % GlobalValues.intNoOfRecordsPerPage > 0)
        {
            intLastPage = intLastPage + 1;
        }

        if (intLastPage == 0) intLastPage = 1;

        int iStartPage = int.Parse(hihCurrentPageId.Value) + 1;

        if (iStartPage > intLastPage) iStartPage = intLastPage;

        if (Count != intLastPage && (intLastPage != 1 || Count != 1))
            lblPageNoInfo.Text = "Page " + iStartPage + " of " + intLastPage.ToString();
        string sImageURL = "Images/navi_disabled.gif";

        if (iStartPage == intLastPage)
        {
            btnNext.Enabled = false;
            btnLast.Enabled = false;
            btnNext.ImageUrl = sImageURL;
            btnLast.ImageUrl = sImageURL;
        }
        else
        {
            btnNext.Enabled = true;
            btnLast.Enabled = true;
            btnNext.ImageUrl = "Images/front.gif";
            btnLast.ImageUrl = "Images/last.gif";
        }

        if (hihCurrentPageId.Value == "0")
        {
            btnFirst.Enabled = false;
            btnPrev.Enabled = false;
            btnFirst.ImageUrl = sImageURL;
            btnPrev.ImageUrl = sImageURL;
        }
        else
        {
            btnFirst.Enabled = true;
            btnPrev.Enabled = true;
            btnFirst.ImageUrl = "Images/first.gif";
            btnPrev.ImageUrl = "Images/back.gif";
        }
    }

    private void loadTables()
    {
        try
        {
            LogWriter.WriteLog("Workflow Item Data Loading: Start" + DateTime.Now.ToLongDateString() + DateTime.Now.ToLongTimeString());
            HDDatabase HDD = new HDDatabase();
            //string sSql = "Select * from sy_workitem_user";

            string sSql = "";
            if (Session["user_afrowid"].ToString() != "650a333596bbf348")
            {
                sSql = "Select * from sy_workitem_user where for_user = '" + Session["user_afrowid"].ToString() + "'";
            }
            else
            {
                sSql = "Select * from sy_workitem_user where for_user = '" + Session["user_afrowid"].ToString() + "' or for_user is null";
            }
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_workitem_user").Copy());
            //addWorkitemForUserTable(ref dsWorkflow,ref HDD);

            DataTable dtItem = dsWorkflow.Tables["sy_workitem_user"].Copy();

            string strMenuRowId = getDistinct(dtItem, "menu_id"); // Added by srivatsan on June 25 2009
            string strpageset = getDistinct(dtItem, "pageset_id");
            string strrulesetid = getDistinct(dtItem, "ruleset_id");
            string strkey_value = getDistinct(dtItem, "key_value");


            dtItem = processWorkItem(ref dtItem);

            sSql = "Select * from sy_obj_acc where role_id ='" + Session["roleid"].ToString() + "'";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_obj_acc").Copy());

            string sUserDisplayName = getUserDisplayName();
            sSql = "select *, " + sUserDisplayName + " as display_name from sy_usr";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_usr").Copy());
            sSql = "select * from sy_org_structure";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_org_structure").Copy());
            //sSql = "select * from sy_mnu";
            sSql = "select * from sy_mnu where af_row_id in (" + strMenuRowId + ")";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_mnu").Copy());
            //sSql = "select * from sy_pgs";
            sSql = "select * from sy_pgs where af_row_id in (" + strpageset + ")";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_pgs").Copy());
            //sSql = "select * from sy_workflow_history";
            sSql = "select * from sy_workflow_history where ruleset_id in (" + strrulesetid + ") and key_value in (" + strkey_value + ")";
            dsWorkflow.Tables.Add(HDD.GetDataTable(sSql, "sy_workflow_history").Copy());

            // Cut and pasted by srivatsan

            // Added by srivatsan

            //processDataSet(ref dtItem);
            //dtItem = processWorkItemOrder(ref dtItem);
            //



            dtItem.TableName = "workitem"; // jus for reference
            dsWorkflow.Tables.Add(dtItem);

            string[] sourceTables = new string[] { "workitem", "sy_obj", "sy_obj", "sy_pg", "sy_pg_ctr", "workitem", "sy_workflow_ruleset", "sy_workflow_ruleset", "sy_workflow_rules", /*"sy_workflow_ruleset",*/ "sy_workflow_rules", "sy_workflow_rules", "workitem", "sy_workflow_rules" };//1
            string[] sourceField = new string[] { "object_id", "af_row_id", "object_id", "af_row_id", "control_bind_id", "ruleset_id", "af_row_id", "af_row_id", "af_row_id", /*"af_row_id",*/ "af_row_id", "af_row_id", "ruleset_id", "af_row_id" };//1
            string[] targetTables = new string[] { "sy_obj", "sy_obj_att", "sy_pg", "sy_pg_ctr", "sy_bnd", "sy_workflow_ruleset", "sy_workflow_rules", "sy_workflow_triggers", "sy_work_item", /*"sy_workflow_history",*/ "sy_field_updates", "sy_reject_field_updates", "sy_workitem_additional_info", "sy_workflow_action" };//Added by John Sathish
            string[] targetField = new string[] { "af_row_id", "object_id", "object_id", "page_id", "af_row_id", "af_row_id", "ruleset", "ruleset_id", "rule_id", /*"ruleset_id",*/ "rule_id", "rule_id", "ruleset_id", "rule_id" };

            //string temp = ""; string sql = "";
            StringBuilder temp = new StringBuilder(); string sql = "";
            for (int i = 0; i < targetTables.Length; i++)
            {
                temp = new StringBuilder(); sql = "";
                // Commented by srivatsan
                //foreach (DataRow dr in dsWorkflow.Tables[sourceTables[i]].Rows)
                //{
                //    //temp += "'" + dr[sourceField[i]].ToString() + "',";
                //    temp.Append("'" + dr[sourceField[i]].ToString() + "',");
                //}
                ////temp = temp.TrimEnd(',');

                foreach (DataRow dr in dsWorkflow.Tables[sourceTables[i]].Rows) // Added by srivatsan
                {
                    if (dr[sourceField[i]].ToString() != "")
                    {
                        if (!temp.ToString().Contains(dr[sourceField[i]].ToString()))
                        {
                            temp.Append("'" + dr[sourceField[i]].ToString() + "',");
                        }
                    }
                }


                sql = "select * from [" + targetTables[i].ToString() + "]" + ((temp.ToString().TrimEnd(',').Length > 0) ? (" where " + targetField[i] + " IN (" + temp.ToString().TrimEnd(',') + ")") : "");
                //fetch and load

                if (!dsWorkflow.Tables.Contains(targetTables[i]))
                {
                    dsWorkflow.Tables.Add(HDD.GetDataTable(sql, targetTables[i].ToString()).Copy());
                }
            }

            processDataSet(ref dtItem);
            if (!Page.IsPostBack)
            {
                loadobjectscombo();
                loadfilter();

            }
            dtItem = processWorkItemOrder(ref dtItem);
            dsWorkflow.Tables.Remove("workitem");

            dtItem.TableName = "workitem"; // jus for reference
            dsWorkflow.Tables.Add(dtItem);




            addWorkitemForUserTable(ref dsWorkflow, "workitem");

            dtItem.Dispose();
            dtItem = null;

            // end of cut




            loadWorkItemObjectRecords(dsWorkflow, "workitem");
            LogWriter.WriteLog("Workflow Item Data Loading: End" + DateTime.Now.ToLongDateString() + DateTime.Now.ToLongTimeString());
        }
        catch (Exception Ex)
        {
            LogWriter.WriteLog("loadTables : Error :" + Ex.Message);
        }
    }


    protected void Page_UnLoad(object sender, EventArgs e)
    {
        dsWorkflow.Dispose();
        dsWorkflow = null;
        HDD = null;
    }

    private string getPreviousOrNextRuleId(DataRow drRule, int iRuleNo)
    {
        string sReturn = "";
        try
        {
            DataRow[] drr = dsWorkflow.Tables["sy_workflow_rules"].Select("ruleset='" + drRule["ruleset_id"].ToString() + "'");
            int iMaxSeq = drr.Length;
            int iSeq = int.Parse(getColumnValue("sy_workflow_rules", "af_row_id", drRule["rule_id"].ToString(), "sequence"));
            if (iSeq > 0 && iSeq < iMaxSeq - 1) iSeq = iSeq + iRuleNo;
            drr = dsWorkflow.Tables["sy_workflow_rules"].Select("ruleset='" + drRule["ruleset_id"].ToString() + "' and sequence = " + iSeq);
            if (drr.Length > 0)
                sReturn = drr[0]["af_row_id"].ToString();
        }

        catch (Exception ex)
        {
            sReturn = drRule["rule_id"].ToString();
            LogWriter.WriteLog("getPreviousOrNextRuleId : Error :" + ex.Message);
        }
        return sReturn;
    }

    private string getPreviousRuleId(DataRow drRule, string Field)
    {
        string sReturn = "";
        try
        {
            DataRow[] drr = dsWorkflow.Tables["sy_workflow_rules"].Select("ruleset='" + drRule["ruleset_id"].ToString() + "'");
            int iMaxSeq = drr.Length;
            int iSeq = int.Parse(getColumnValue("sy_workflow_rules", "af_row_id", drRule["rule_id"].ToString(), "sequence"));
            iSeq = iSeq - 1;
            drr = dsWorkflow.Tables["sy_workflow_rules"].Select("ruleset='" + drRule["ruleset_id"].ToString() + "' and sequence = " + iSeq);
            if (drr.Length > 0)
                sReturn = drr[0][Field].ToString();
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("getPreviousRuleId : Error :" + ex.Message);
        }
        return sReturn;
    }

    private string getNextRuleId(DataRow drRule, string Field)
    {
        string sReturn = "";
        try
        {
            DataRow[] drr = dsWorkflow.Tables["sy_workflow_rules"].Select("ruleset='" + drRule["ruleset_id"].ToString() + "'");
            int iMaxSeq = drr.Length;
            int iSeq = int.Parse(getColumnValue("sy_workflow_rules", "af_row_id", drRule["rule_id"].ToString(), "sequence"));
            iSeq = iSeq + 1;
            drr = dsWorkflow.Tables["sy_workflow_rules"].Select("ruleset='" + drRule["ruleset_id"].ToString() + "' and sequence = " + iSeq);
            if (drr.Length > 0)
                sReturn = drr[0][Field].ToString();
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("getPreviousRuleId : Error :" + ex.Message);
        }
        return sReturn;
    }

    private string getWorkflowRejectUser(string Ruleset_id, string sRule_Id)
    {
        string sUser = "";
        try
        {
            string sPreviousRuleTarget = getColumnValue("sy_workflow_rules", "af_row_id", sRule_Id, "Target").ToLower();
            string sTitle = "";
            if (sPreviousRuleTarget == "user_from_field")
                sTitle = getColumnValue("sy_workflow_rules", "af_row_id", sRule_Id, "target_title").ToLower();
            else
                sTitle = getColumnValue("sy_usr", "af_row_id", Session["user_afrowid"].ToString(), "title");
            //sCurrentUserTitle = getColumnValue("sy_org_structure", "af_row_id", sCurrentUserTitle, "title");
            DataRow[] drr = dsWorkflow.Tables["sy_workflow_history"].Select("workflow_history_ref='" + sWorkflowHistory_ref + "' and status='submitted' and target_title='" + sTitle + "'", "created_at desc");

            if (drr.Length > 0)
                sUser = drr[0]["created_by"].ToString();
            //else
            //    if (sUser == "")
            //        sUser = "650a333596bbf348";
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("getWorkflowRejectUser : Error :" + ex.Message);
            //sUser = "650a333596bbf348";
        }

        return sUser;
    }

    private string getUserFromData(DataRow dr)
    {
        string sUser = "";
        try
        {
            string sObject_Table = getColumnValue("sy_obj", "af_row_id", dsInputInfo.Tables[0].Rows[0]["object_id"].ToString(), "object_table");
            string sAttrib_afrowid = getColumnValue("sy_workflow_rules", "af_row_id", dr["af_row_id"].ToString(), "target_title");
            string sAttrib_Id = getColumnValue("sy_obj_att", "af_row_id", sAttrib_afrowid, "attrib_id");
            string sKeyValue = dsInputInfo.Tables[0].Rows[0]["key_value"].ToString();
            string sSql = "select " + sAttrib_Id + " from " + sObject_Table + " where af_row_id = '" + sKeyValue + "'";
            string sTemp = HDD.GetColumnValue(sSql);
            sUser = getColumnValue("sy_usr", "af_row_id", sTemp, "af_row_id");
            if (sUser == "")
                sUser = getColumnValue("sy_usr", "user_id", sTemp, "af_row_id");
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("getDatarow : Error :" + ex.Message);
        }
        return sUser;
    }

    private string getWorkitemUser(DataRow drRule, ref DataRow drHistory)
    {
        string sUser = "";
        try
        {
            string sBased_On = getColumnValue("sy_workflow_ruleset", "af_row_id", drRule["ruleset"].ToString(), "based_on").ToLower();
            string sTarget = getColumnValue("sy_workflow_rules", "af_row_id", drRule["af_row_id"].ToString(), "Target").ToLower();

            string sRule_Id = getPreviousRuleId(drHistory, "af_row_id");

            string sPreviousRuleTarget = getColumnValue("sy_workflow_rules", "af_row_id", sRule_Id, "Target").ToLower();

            if (sPreviousRuleTarget == "user_from_field")
            {
                string sPrevious = getPreviousSubmittedUser(sRule_Id, drRule["target_title"].ToString());
                sUser = getReportingToUser(sPrevious, drRule["target_title"].ToString(), 0);

                if (sUser == Session["user_afrowid"].ToString())
                {
                    sRule_Id = getNextRuleId(drHistory, "af_row_id");

                    if (sRule_Id != "")
                    {
                        drHistory["rule_id"] = sRule_Id;
                        drHistory["source_title"] = getColumnValue("sy_workflow_rules", "af_row_id", sRule_Id, "source_title");
                        drHistory["target_title"] = getColumnValue("sy_workflow_rules", "af_row_id", sRule_Id, "target_title");
                        sUser = getWorkitemUser(dsWorkflow.Tables["sy_workflow_rules"].Select("af_row_id='" + sRule_Id + "'")[0], ref drHistory);
                    }
                    else
                    {
                        drHistory.Table.Rows.Remove(drHistory);
                        drHistory.Table.Rows[0]["status"] = "Completed";
                        //deleteWorkItemForUser(drHistory.Table.Rows[0]["ruleset_id"].ToString(), drHistory.Table.Rows[0]["rule_id"].ToString(), drHistory.Table.Rows[0]["key_value"].ToString(), Session["user_afrowid"].ToString());
                    }
                }
            }
            else
            {
                if (sTarget == "user_from_field")
                {
                    sUser = getUserFromData(drRule);
                    /*if (sUser == HttpContext.Current.Session["user_afrowid"].ToString())
                    {
                        string sRuleId = getPreviousOrNextRuleId(drRule, 1);
                        getWorkitem_User(drRuleChange);
                    }*/
                }
                else if (/*sBased_On*/sTarget == "user") // Modified by 'Imran Gani' on 27-Mar-2013, to compare with target user.
                    sUser = getColumnValue("sy_workflow_rules", "af_row_id", drRule["af_row_id"].ToString(), "target_title");
                else if (/*sBased_On*/sTarget == "title") // Modified by 'Imran Gani' on 27-Mar-2013, to compare with target title.
                {
                    string sSkip_Rule = getColumnValue("sy_workflow_ruleset", "af_row_id", drRule["ruleset"].ToString(), "skip_title").ToLower();

                    if (sSkip_Rule == "true")
                        sUser = getTargetUserBasedOnRule(drRule["af_row_id"].ToString());
                    else if (sSkip_Rule == "false" || sSkip_Rule == "")
                    {
                        string sTarget_Title = getColumnValue("sy_workflow_rules", "af_row_id", drRule["af_row_id"].ToString(), "target_title");
                        string sTempUser = getColumnValue("sy_usr", "af_row_id", HttpContext.Current.Session["user_afrowid"].ToString(), "reporting_to");
                        string sTempTitle = getColumnValue("sy_usr", "af_row_id", sTempUser, "title");
                        if (sTarget_Title == sTempTitle) sUser = sTempUser;
                    }
                }
            }
            string sTemp = getColumnValue("sy_usr", "af_row_id", sUser, "af_row_id");
            //if (sTemp == "") sUser = "650a333596bbf348";
            //if (sUser == "") sUser = "650a333596bbf348";
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("getWorkitem_User : Error :" + ex.Message);
            //sUser = "650a333596bbf348";
        }
        return sUser;
    }

    private string getTargetUserBasedOnRule(string Rule_Id)
    {
        string sUser = "";
        try
        {
            string sTarget_Title = getColumnValue("sy_workflow_rules", "af_row_id", Rule_Id, "target_title");
            sUser = getReportingToUser(HttpContext.Current.Session["user_afrowid"].ToString(), sTarget_Title, 0);
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("getTargetTitleBasedOnRule : Error : " + ex.Message);
        }
        return sUser;
    }

    private string getReportingToUser(string User_afrowid, string Target_Title, int IterationNumber)
    {
        string sUser = "";
        try
        {
            DataRow[] drr = dsWorkflow.Tables["sy_usr"].Select("af_row_id='" + User_afrowid + "'");
            if (IterationNumber <= dsWorkflow.Tables["sy_usr"].Rows.Count)
            {
                if (drr.Length > 0)
                {
                    string sTitle_afrowid = drr[0]["title"].ToString();
                    //string sTitle = getColumnValue("sy_org_structure", "af_row_id", sTitle_afrowid, "title").ToLower();

                    if (sTitle_afrowid == "") return sUser;

                    if (sTitle_afrowid == Target_Title)
                        sUser = User_afrowid;
                    else
                        sUser = getReportingToUser(drr[0]["reporting_to"].ToString(), Target_Title, IterationNumber + 1);
                }
            }
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("getReportingToTitle : Error :" + ex.Message);
        }
        return sUser;
    }

    private string getPreviousSubmittedRuleId()
    {
        string sRuleId = "";
        try
        {
            string sCurrentUserTitle = getColumnValue("sy_usr", "af_row_id", Session["user_afrowid"].ToString(), "title");
            //sCurrentUserTitle = getColumnValue("sy_org_structure", "af_row_id", sCurrentUserTitle, "title");
            //DataRow[] drr = dsWorkflow.Tables["sy_workflow_history"].Select("workflow_history_ref='" + sWorkflowHistory_ref + "' and status='submitted' and target_title='" + sCurrentUserTitle + "'", "created_at desc");
            DataRow[] drr = dsWorkflow.Tables["sy_workflow_history"].Select("workflow_history_ref='" + sWorkflowHistory_ref + "' and status='submitted'", "created_at desc");

            if (drr.Length > 0)
                sRuleId = drr[0]["rule_id"].ToString();
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("getPreviousSubmittedRuleId : Error :" + ex.Message);
        }

        return sRuleId;
    }

    private string getPreviousSubmittedRuleId(string sRuleId)
    {
        try
        {
            //string sCurrentUserTitle = getColumnValue("sy_usr", "af_row_id", Session["user_afrowid"].ToString(), "title");
            //sCurrentUserTitle = getColumnValue("sy_org_structure", "af_row_id", sCurrentUserTitle, "title");
            DataRow[] drr = dsWorkflow.Tables["sy_workflow_history"].Select("workflow_history_ref='" + sWorkflowHistory_ref + "' and status='submitted' and rule_id='" + sRuleId + "'", "created_at desc");

            if (drr.Length > 0)
                sRuleId = drr[0]["rule_id"].ToString();
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("getPreviousSubmittedRuleId : Error :" + ex.Message);
        }

        return sRuleId;
    }

    private string getPreviousSubmittedUser(string PreviousRuleId, string TargetTitle)
    {
        string sUser = "";
        try
        {
            DataRow[] drr = dsWorkflow.Tables["sy_workflow_history"].Select("workflow_history_ref='" + sWorkflowHistory_ref + "' and rule_id='" + PreviousRuleId + "' and status='submitted'", "created_at desc");

            if (drr.Length > 0)
                sUser = drr[0]["created_by"].ToString();
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("getPreviousSubmittedUser : Error :" + ex.Message);
        }

        return sUser;
    }

    private void ApproveAll(ControlCollection frmControl, ref List<string> sKeys)
    {
        try
        {
            foreach (Control con in frmControl)
            {
                if (con is CheckBox)
                {
                    CheckBox chk = (CheckBox)con;

                    if (chk.Checked)
                    {
                        if (chk.ID.ToLower() != "chk_")
                            sKeys.Add(chk.ID.ToLower().Replace("chk_", ""));
                    }
                }
                else
                    ApproveAll(con.Controls, ref sKeys);
            }
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("ApproveAll" + Ex.Message);
        }
    }

    protected void btnApproveAll_Click(object sender, EventArgs e)
    {
        List<string> sKeys = new List<string>();
        ApproveAll(this.Controls, ref sKeys);
        SaveAll(ref sKeys);
        RedirectPage();
        //Response.Redirect(Request.Url.AbsoluteUri, false);
    }

    private bool SaveAll(ref List<string> sKeys)
    {
        bool bReturn = false;
        try
        {
            string StatusCode = "Approved";
            dsInputInfo = new DataSet();
            DataSet dsPageInfo = null;
            getPageInfo(ref dsPageInfo);
            ScreenReader SR = new ScreenReader();
            SR.CreatePageDataTable(dsInputInfo, dsPageInfo.Tables["page"], dsPageInfo.Tables["pagectrl"]);
            List<string> SQLs = new List<string>();

            foreach (string Key_Value in sKeys)
            {
                string sRule_id = "";
                DataRow drData = getWorkFlowHistoryRecord(Key_Value);//getting the record from sy_workflow_history
                if (drData != null)
                {
                    if (drData["status"].ToString().ToLower() == "rejected") // if rejected the previous rule id should be found to create a rejected workflow for the user who submitted the work item
                        sRule_id = getPreviousRuleId(drData, "af_row_id");
                    else
                        sRule_id = drData["rule_id"].ToString();

                    DataRow dr = dsInputInfo.Tables["p_workflow_history"].NewRow();
                    sWorkflowHistory_ref = drData["workflow_history_ref"].ToString();
                    dr["af_row_id"] = SR.getHashKey(); ;
                    dr["ruleset_id"] = drData["ruleset_id"];
                    dr["rule_id"] = sRule_id;
                    dr["object_id"] = drData["object_id"];
                    dr["pageset_id"] = drData["pageset_id"];
                    dr["menu_id"] = drData["menu_id"];
                    dr["key_value"] = drData["key_value"];
                    dr["status"] = StatusCode;
                    dr["source_title"] = drData["source_title"];
                    dr["target_title"] = drData["target_title"];
                    dr["comments"] = hfComments.Value;
                    dr["workflow_history_ref"] = drData["workflow_history_ref"];
                    dr["created_by"] = HttpContext.Current.Session["user_afrowid"].ToString();
                    dr["owner_id"] = HttpContext.Current.Session["user_afrowid"].ToString();// Added by srivatsan on June 15 2009 for owner id field
                    dr["modified_by"] = HttpContext.Current.Session["user_afrowid"].ToString();
                    dr["created_at"] = DateTime.Now.ToString();
                    dr["modified_at"] = DateTime.Now.ToString();
                    dr["object_record_created_at"] = drData["object_record_created_at"]; // Added by srivatsan
                    dr["object_record_created_by"] = drData["object_record_created_by"];


                    dsInputInfo.Tables["p_workflow_history"].Rows.Add(dr);
                    if (StatusCode == "Approved")
                    {
                        dr["is_active"] = "false";
                        checkForNextRule(drData, dr, ref dsInputInfo);
                    }
                    else if (StatusCode == "Rejected")
                    {
                        checkForPreviousRule(drData, dr, ref dsInputInfo);
                        dr["is_active"] = "true";
                    }

                    UpdateFlagandFieldForAll(ref drData, Key_Value, ref SQLs);
                }
            }

            bReturn = UpdateDB(ref dsPageInfo, ref SQLs);
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("SaveAll : Error : " + ex.Message);
        }
        return bReturn;
    }

    private void UpdateFlagandFieldForAll(ref DataRow drData, string Key_Value, ref List<string> SQLs)
    {
        try
        {
            string sSql = "update sy_workflow_history set is_active='0' where af_row_id='" + Key_Value + "'";
            SQLs.Add(sSql);
            sSql = "delete from sy_workitem_user where af_row_id='" + Key_Value + "'";
            SQLs.Add(sSql);

            DataRow[] drField = null;

            string sRule_Id = drData["rule_id"].ToString();
            if (drData["status"].ToString().ToLower() == "rejected")
                //{
                // on rejection of a work item, we have to get the appropriate work item reject field update data
                sRule_Id = getPreviousRuleId(drData, "af_row_id");
            //    drField = dsWorkflow.Tables["sy_reject_field_updates"].Select("rule_id='" + sRule_Id + "'");
            //}
            //else

            drField = dsWorkflow.Tables["sy_field_updates"].Select("rule_id='" + sRule_Id + "'");// rejection of an workitem is never goin to be handled in this function coz this is a function to approve all

            if (drField != null)
                if (drField.Length > 0)
                    if (drField[0]["field_name"].ToString() != "")
                    {
                        string object_table = getColumnValue("sy_obj", "af_row_id", drData["object_id"].ToString(), "object_table");
                        sSql = "update [" + object_table + "] set [" + drField[0]["field_name"].ToString() + "]='" + drField[0]["field_value"].ToString() + "' where af_row_id='" + drData["key_value"].ToString() + "'";
                        SQLs.Add(sSql);
                    }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("UpdateFlagandFieldForAll : Error : " + ex.Message);
        }
    }

    private bool UpdateDB(ref DataSet dsPageInfo, ref List<string> SQLs)
    {
        bool bReturn = false;
        try
        {
            Master objMaster = new Master();
            bReturn = objMaster.ProcessOperation(dsPageInfo, dsInputInfo, "INSERT");

            if (bReturn)
            {
                StringBuilder sbTemp = new StringBuilder();
                foreach (string sSql in SQLs)
                    sbTemp.Append(sSql).Append(";");

                SqlCommand sqlComm = new SqlCommand(sbTemp.ToString());
                int iRet = HDD.ExecuteCmdObj(sqlComm);
                sqlComm = null;
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("UpdateDB : Error : " + ex.Message);
        }
        return bReturn;
    }
    protected void btnPrev_Click(object sender, ImageClickEventArgs e)
    {
        //if record not exists, add
    }
    protected void btnNext_Click(object sender, ImageClickEventArgs e)
    {
        //if record not exists, minus
    }

    private void loadobjectscombo()
    {
        try
        {
            ddobject.Visible = true;
            if (dsWorkflow != null)
            {
                //if (dsWorkflow.Tables["sy_workitem_user"] != null)
                if (dsWorkflow.Tables["workitem"] != null)
                {

                    //ddobject.Items.Clear();

                    ddobject.Items.Add(GlobalValues.ShowAll);

                    //foreach (DataRow dr in dsWorkflow.Tables["sy_workitem_user"].Rows)
                    foreach (DataRow dr in dsWorkflow.Tables["workitem"].Rows)
                    {
                        ListItem list1 = new ListItem();
                        list1.Value = dr["object_id"].ToString();
                        list1.Text = HDD.GetColumnValue("sy_obj", "object_Name", "af_row_id = '" + dr["object_id"].ToString() + "'");
                        if (!ddobject.Items.Contains(list1))
                        {
                            ddobject.Items.Add(list1);
                        }
                    }

                    if (Request.QueryString["ddobj"] != null)
                    {
                        ddobject.SelectedValue = Request.QueryString["ddobj"].ToString();
                    }
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error loading objects on combo :" + ex.Message);
        }
    }

    private bool isBeingSorted(string tableName, string colName)
    {
        if (HttpContext.Current.Session["datasort"] == null) return false;
        string datasort = HttpContext.Current.Session["datasort"].ToString();
        if (datasort == "") return false;
        return (getTableName(datasort) == tableName && colName == getColName(datasort));
    }

    private bool isDescending()
    {
        if (HttpContext.Current.Session["datasort"] == null) return false;
        string datasort = HttpContext.Current.Session["datasort"].ToString();
        if (datasort == "") return false;
        return datasort.IndexOf(" desc") > 0;
    }

    private string getColName(string content)
    {
        try
        {
            string colName = "";
            colName = content.Substring(content.IndexOf("].[") + 3);
            colName = colName.Substring(0, colName.IndexOf("]"));
            return colName;
        }
        catch (Exception e)
        {
            return "";
        }
    }
    private string getTableName(string content)
    {
        try
        {
            string tableName = "";
            tableName = content.Substring(0, content.IndexOf("].["));
            tableName = tableName.Trim('[').Trim(']');
            return tableName;
        }
        catch (Exception e)
        {
            return "";
        }
    }

    private TableCell getHeaderCell(string ColumnName)
    {
        TableCell htc = new TableCell();  // Added by srivatsan

        string sortorder1 = "";
        string sortdata1 = "";

        ColumnName = ColumnName.ToLower().Replace("&nbsp;", "");

        if (Session["datasort"] != null)
        {
            sortdata1 = Session["datasort"].ToString().ToLower();
            if (Session["datasortorder"] != null)
            {
                sortorder1 = Session["datasortorder"].ToString().ToLower();
            }
        }




        LinkButton lnkbutton = new LinkButton();
        lnkbutton.Text = ColumnName;
        lnkbutton.ForeColor = System.Drawing.Color.White;
        lnkbutton.OnClientClick = "javascript:sortdata('" + ColumnName.ToLower() + "','" + sortorder1 + "','" + sortdata1 + "');";
        lnkbutton.ID = "lnk_" + ColumnName.ToLower();

        bool isDesc = isDescending();






        //if (isBeingSorted("sy_workitem_user", ColumnName))
        //{
        // bool isDesc = isDescending();
        // lnkbutton.ToolTip = isDesc ? "Sorted Descending" : "Sorted Ascending";
        // htc.Controls.Add(lnkbutton);
        //htc.Controls.Add(new LiteralControl("&nbsp;<img " + (isDesc == true ? "src=\"Images/sort_down.gif\"" : "src=\"Images/sort_up.gif\"") + " border=\"0\" />"));
        //}
        //else
        //  htc.Controls.Add(lnkbutton);

        if (ColumnName == "&nbsp;")
            htc.Width = Unit.Pixel(25); //"25px";



        htc.Controls.Add(lnkbutton);

        if (Session["datasort"] != null)
        {
            if (Session["datasortorder"] != null)
            {
                string sortorder = Session["datasortorder"].ToString().ToLower();
                if (Session["datasort"].ToString() == ColumnName.ToLower())
                {
                    htc.Controls.Add(new LiteralControl("&nbsp;<img " + (sortorder == "desc" ? "src=\"Images/sort_down.gif\"" : "src=\"Images/sort_up.gif\"") + " border=\"0\" />"));
                    if (sortorder != "")
                    {
                        lnkbutton.ToolTip = sortorder == "desc" ? "Sorted Descending" : "Sorted Ascending";
                    }
                }

            }
        }
        return htc;
    }


    private DataTable processDataSet(ref DataTable dt)
    {
        try
        {
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {


                    dt.Columns.Add("temp_worktitemmessage");
                    dt.Columns.Add("temp_description");
                    dt.Columns.Add("temp_status");
                    dt.Columns.Add("temp_comments");
                    dt.Columns.Add("temp_by");
                    dt.Columns.Add("temp_dated", System.Type.GetType("System.DateTime"));

                    Localization li = new Localization();
                    DynamicDisplayI DDI = new DynamicDisplayI();

                    //ds.Tables.Add(dttemp);


                    foreach (DataRow dr in dt.Rows)
                    {
                        //added by srivatsan on Mar 02 2009
                        string sWorkMessage = getColumnValue("sy_work_item", "rule_id", dr["rule_id"].ToString(), "message");
                        dr["temp_worktitemmessage"] = sWorkMessage;
                        dr["temp_status"] = dr["status"].ToString();
                        dr["temp_comments"] = dr["comments"].ToString();
                        string sUTC_Offset = DDI.getUserSetting("sy_usr", "time_zone", "sy_timezone", "utc_offset", "timezone_id");
                        string datetime = li.getDateTime4TimeZone(dr["created_at"].ToString(), sUTC_Offset, false, true);

                        //DateTime dtdatetime;
                        // dtdatetime = DateTime.Parse(dr["created_at"].ToString());
                        // Localization loc = new Localization();

                        //string result = result.ToString(loc.getDateTimeFormat());

                        //dr["temp_dated"] = Convert.ToDateTime(datetime, li.getUserCulture()); // Commented by 'Imran Gani' on 28-Feb-2014, for MBLocale
                        dr["temp_dated"] = Convert.ToDateTime(datetime, MBLocalization.getCultureInfo(HttpContext.Current.Session["userlocale"].ToString())); // Added by 'Imran Gani' on 28-Feb-2014, for MBLocale
                        // dr["temp_dated"] = dr["created_at"];
                        string sValue = getColumnValue("sy_usr", "af_row_id", dr["created_by"].ToString().ToString(), "display_name");
                        dr["temp_by"] = sValue;
                        string description = getColumnValue("sy_workflow_ruleset", "af_row_id", dr["ruleset_id"].ToString().ToString(), "name");
                        dr["temp_description"] = description;
                        //
                        //  getColumnValue("sy_work_item", "rule_id", sRule_Id, "message")

                    }


                    li = null;
                    DDI = null;



                    //return dt;

                }
            }


        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error :ProcessDataSet: " + ex.Message);
        }
        return dt;

    }



    private DataTable processWorkItemOrder(ref DataTable dtItem)
    {
        DataTable dtWorkItem = dtItem.Clone();
        try
        {

            DataRow[] drr = null;

            string filteritem = "temp_dated";
            if (Session["datasort"] != null)
            {
                string sortvalue = Session["datasort"].ToString().ToLower();
                switch (sortvalue)
                {
                    case "dated":
                        filteritem = "temp_dated";
                        break;
                    case "by":
                        filteritem = "temp_by";
                        break;
                    case "status":
                        filteritem = "temp_status";
                        break;
                    case "comments":
                        filteritem = "temp_comments";
                        break;
                    case "workitem message":
                        filteritem = "temp_worktitemmessage";
                        break;
                    case "description":
                        filteritem = "temp_description";
                        break;
                    default:
                        filteritem = "temp_dated";
                        break;
                }
            }

            string filterorder = "desc";
            if (Session["datasortorder"] != null)
            {
                if (Session["datasortorder"].ToString() != "")
                {
                    if (Session["datasortorder"].ToString() != "")
                    {
                        filterorder = Session["datasortorder"].ToString().ToLower();
                    }
                }
            }


            string sqlfilter = "";

            if (ddobject.SelectedIndex > 0)
            {
                sqlfilter += " and object_id = '" + ddobject.SelectedValue + "'";
            }
            if (ddcreatedby.SelectedIndex > 0)
            {
                sqlfilter += " and object_record_created_by='" + ddcreatedby.SelectedValue + "'";
            }
            if (ddcreatedat.SelectedIndex > 0)
            {
                string startdate = ddcreatedat.SelectedValue + " 00:00:00";
                string enddate = ddcreatedat.SelectedValue + " 23:59:59";
                sqlfilter += " and (object_record_created_at >= '" + startdate + "' and object_record_created_at <= '" + enddate + "')";
            }


            if (Session["user_afrowid"].ToString() == "650a333596bbf348")
            {

                //if (ddobject.SelectedIndex < 1)
                //{
                //drr = dtItem.Select("for_user='" + Session["user_afrowid"].ToString() + "' or for_user='' or for_user is null", "" + filteritem + " "+filterorder+""); // modified by srivatsan before it was desc
                //}
                //else
                //{
                drr = dtItem.Select("(for_user='" + Session["user_afrowid"].ToString() + "' or (for_user='' or for_user is null)) " + sqlfilter + "", "" + filteritem + " " + filterorder + ""); // modified by srivatsan before it was desc
                //}
            }
            else
            {
                // Modified by srivatsan for filter in workitem
                //if (ddobject.SelectedIndex < 1)
                //{
                //    drr = dtItem.Select("for_user='" + Session["user_afrowid"].ToString() + "'", ""+filteritem+" "+filterorder+""); // Modified by srivatsan before it was desc
                //}
                //else
                //{
                drr = dtItem.Select("for_user='" + Session["user_afrowid"].ToString() + "' " + sqlfilter + "", "" + filteritem + " " + filterorder + ""); // Modified by srivatsan before it was desc
                // }
            }
            getTableFromArray(ref dtWorkItem, drr);

            drr = null;
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("processWorkItem : Error : " + Ex.Message);
        }
        return dtWorkItem;
    }
    //private DataTable processWorkItemOrder(ref DataTable dtItem)
    //{
    //    DataTable dtWorkItem = dtItem.Clone();
    //    try
    //    {

    //        DataRow[] drr = null;

    //        string filteritem = "temp_dated";
    //        if (Session["datasort"] != null)
    //        {
    //            string sortvalue = Session["datasort"].ToString().ToLower();
    //            switch (sortvalue)
    //            {
    //                case "dated":
    //                    filteritem = "temp_dated";
    //                    break;
    //                case "by":
    //                    filteritem = "temp_by";
    //                    break;
    //                case "status":
    //                    filteritem = "temp_status";
    //                    break;
    //                case "comments":
    //                    filteritem = "temp_comments";
    //                    break;
    //                case "workitem message":
    //                    filteritem = "temp_worktitemmessage";
    //                    break;
    //                case "description":
    //                    filteritem = "temp_description";
    //                    break;
    //                default:
    //                    filteritem = "temp_dated";
    //                    break;
    //            }
    //        }

    //        string filterorder = "asc";
    //        if (Session["datasortorder"] != null)
    //        {
    //            if (Session["datasorder"] != "")
    //            {
    //                filterorder = Session["datasortorder"].ToString().ToLower();
    //            }
    //        }


    //        if (Session["user_afrowid"].ToString() == "650a333596bbf348")
    //        {

    //            if (ddobject.SelectedIndex < 1)
    //            {
    //                drr = dtItem.Select("for_user='" + Session["user_afrowid"].ToString() + "' or for_user='' or for_user is null", "" + filteritem + " " + filterorder + ""); // modified by srivatsan before it was desc
    //            }
    //            else
    //            {
    //                drr = dtItem.Select("for_user='" + Session["user_afrowid"].ToString() + "' or (for_user='' or for_user is null) and (object_id='" + ddobject.SelectedValue + "')", "" + filteritem + " " + filterorder + ""); // modified by srivatsan before it was desc
    //            }
    //        }
    //        else
    //        {
    //            // Modified by srivatsan for filter in workitem
    //            if (ddobject.SelectedIndex < 1)
    //            {
    //                drr = dtItem.Select("for_user='" + Session["user_afrowid"].ToString() + "'", "" + filteritem + " " + filterorder + ""); // Modified by srivatsan before it was desc
    //            }
    //            else
    //            {
    //                drr = dtItem.Select("for_user='" + Session["user_afrowid"].ToString() + "' and object_id='" + ddobject.SelectedValue + "'", "" + filteritem + " " + filterorder + ""); // Modified by srivatsan before it was desc
    //            }
    //        }
    //        getTableFromArray(ref dtWorkItem, drr);

    //        drr = null;
    //    }

    //    catch (Exception Ex)
    //    {
    //        LogWriter.WriteLog("processWorkItem : Error : " + Ex.Message);
    //    }
    //    return dtWorkItem;
    //}
    private void loadfilter()
    {
        DynamicDisplayI DDI = new DynamicDisplayI();
        string sUTC_Offset = DDI.getUserSetting("sy_usr", "time_zone", "sy_timezone", "utc_offset", "timezone_id");
        if (dsWorkflow != null)
        {
            if (dsWorkflow.Tables["workitem"] != null)
            {
                //ddcreatedby.Items.Clear();
                //ddcreatedat.Items.Clear();

                ddcreatedby.Items.Add(GlobalValues.ShowAll);
                ddcreatedat.Items.Add(GlobalValues.ShowAll);
                foreach (DataRow dr in dsWorkflow.Tables["workitem"].Rows)
                {

                    try
                    {
                        string created_userold = "";
                        string created_dateold = "";
                        string created_user = dr["object_record_created_by"].ToString();
                        string created_date = dr["object_record_created_at"].ToString();
                        if (created_user != "")
                        {
                            string userdisplayname = getColumnValue("sy_usr", "af_row_id", created_user, "display_name");
                            ListItem lst = new ListItem(userdisplayname, created_user);
                            if (!ddcreatedby.Items.Contains(lst))
                            {
                                ddcreatedby.Items.Add(lst);
                            }
                        }
                        if (created_date != "")
                        {
                            Localization loc = new Localization();
                            string createddate = Convert.ToDateTime(created_date).ToShortDateString();
                            string sconverteddate = loc.getDateTime4TimeZone(created_date, sUTC_Offset, false, true);
                            //string converteddate = Convert.ToDateTime(sconverteddate, loc.getUserCulture()).ToShortDateString(); // Commented by 'Imran Gani' on 28-Feb-2014, for MBLocale
                            string converteddate = Convert.ToDateTime(sconverteddate, MBLocalization.getCultureInfo(HttpContext.Current.Session["userlocale"].ToString())).ToShortDateString(); // Added by 'Imran Gani' on 28-Feb-2014, for MBLocale

                            //string result = Convert.ToDateTime(created_date).ToString(loc.getDateFormat()); // Added by srivatsan for culture conversion // Commented by 'Imran Gani' on 28-Feb-2014, for MBLocale
                            string result =MBLocalization.getLocalizedDate(HttpContext.Current.Session["userlocale"].ToString(), DateTime.Parse(created_date)); // Added by 'Imran Gani' on 28-Feb-2014, for MBLocale

                            //string converteddate = Convert.ToDateTime(loc.getDateTime4TimeZone(created_date, sUTC_Offset, false, true)).ToShortDateString();
                            //ListItem lst1 = new ListItem(converteddate, createddate); // srivatsan Oct 19 2009
                            ListItem lst1 = new ListItem(result, createddate);
                            if (!ddcreatedat.Items.Contains(lst1))
                            {
                                ddcreatedat.Items.Add(lst1);
                            }
                        }

                    }
                    catch (Exception ex)
                    {
                        LogWriter.WriteLog("Error load workitem filter: " + ex.Message);
                    }



                }
                if (Request.QueryString["ddcby"] != null)
                {
                    ddcreatedby.SelectedValue = Request.QueryString["ddcby"].ToString();
                }
                if (Request.QueryString["ddcat"] != null)
                {
                    ddcreatedat.SelectedValue = Request.QueryString["ddcat"].ToString();
                }

            }
        }
    }
    //protected void ddobject_selectedindexchanged(object sender, EventArgs e)
    //{
    //    if (ddobject.SelectedValue != "--All--" || ddcreatedby.SelectedValue != "--All--" || ddcreatedat.SelectedValue != "--All--")
    //    {
    //        hihCurrentPageId.Value = "0";
    //    }
    //}


    private void filldropdowndefault()
    {
        ddcreatedat.SelectedValue = hfddcreatedat.Value;
        ddcreatedby.SelectedValue = hfddcreatedby.Value;
        ddobject.SelectedValue = hfddobject.Value;
    }


    private string getDistinct(DataTable dt, string field) // Get distinct values from the datatable for the field as string
    {
        StringBuilder sb = new StringBuilder();
        string result = "";
        try
        {
            if (dt != null)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    if (dr[field].ToString() != "")
                    {
                        if (!sb.ToString().Contains("'" + dr[field].ToString() + "'"))
                        {
                            sb.Append("'" + dr[field].ToString() + "',");
                        }
                    }
                }
                if (sb.ToString() != "")
                {
                    result = sb.ToString().Substring(0, sb.Length - 1);
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : GetDistinctRecords :" + ex.Message);
        }
        return result;

    }

    private string wrapItAsMessage(string msg, bool isError)
    {
        return "<div class=\"" + (isError == true ? "mbErrorMessage" : "mbMessage") + "\">" + msg + "</div>";
    }

    //Added by John Sathish
    private bool sendEmail(DataRow drWorkflowAction, string strContent, string strEmailTemplate, string object_table, string Row_Key)
    {
        CommonFunctions objCmnFun = new CommonFunctions();
        DataSet ds = new DataSet(); //Added by 'Imran Gnai' on 22/05/12, to send the mail with their required field.
        try
        {
            ds = HDD.GetDataset("Select content, subject from sy_email_template where af_row_id='" + strEmailTemplate + "';select * from " + object_table + " where af_row_id='" + Row_Key + "';select mem_value2 from sy_acr_mem where af_row_id='b13034b44961ff77';");
            DataRow drInputData = ds.Tables[1].Rows[0];
            //Below few lines commanded by 'Imran Gani' on 22/05/12.
            //string strMailContent = (HDD.GetDataTable("Select content from sy_email_template where af_row_id='" + strEmailTemplate + "'")).Rows[0]["content"].ToString();
            //string strFromMailID = HDD.GetColumnValue("select mem_value2 from sy_acr_mem where af_row_id='b13034b44961ff77'");
            //foreach (DataColumn dc in drWorkflowAction.Table.Columns)
            //{
            //    strMailContent = strMailContent.Replace("$content$", drWorkflowAction["field_name"].ToString() + " (Field) from " + strcon[0].ToString() + " is updated to value " + strcon[2].ToString());
            //}
            //HDDatabase HDD = new HDDatabase();

            //Below lines are added by 'Imran Gani' on 22/05/12, to Convert EMail Content.
            string strMailContent = ds.Tables[0].Rows[0]["content"].ToString();
            if (strMailContent != null && strMailContent != "" && drInputData != null)
                strMailContent = ConvertMailContent(drInputData, strMailContent);
            
            string strMailSubject = ds.Tables[0].Rows[0]["subject"].ToString();
            if (strMailSubject != null && strMailSubject != "" && drInputData != null)
                strMailSubject = ConvertMailContent(drInputData, strMailSubject);
                    
            string strFromMailID = ds.Tables[2].Rows[0][0].ToString();
            string strEmailID = string.Empty;
            //string strMailSubject = getColumnValue("sy_workflow_ruleset", "af_row_id", getColumnValue("sy_workflow_rules", "af_row_id", drWorkflowAction["rule_id"].ToString(), "ruleset"), "name"); //line commanded by 'Imran Gani' on 22/05/12.
                    
            string strTargetUser = getColumnValue("sy_workflow_rules", "af_row_id", drWorkflowAction["rule_id"].ToString(), "source_title"); // Changes made by 'Imran Gani' on 17/04/12, to send a mail for the source user.
            string[] strcon = strContent.Split(',');
            strMailContent = strMailContent.Replace("$content$", strcon[1].ToString() + " (Field) in the " + strcon[0].ToString() + " page is updated to value " + strcon[2].ToString() + ".");
            strMailContent = strMailContent.Replace("$Recipient$", getColumnValue("sy_usr", "af_row_id", strTargetUser, "first_name"));
            strMailContent = strMailContent.Replace("$User$", HDD.GetColumnValue("select mem_value3 from sy_acr_mem where af_row_id='b13034b44961ff77'"));
            string sBased_On = getColumnValue("sy_workflow_rules", "af_row_id", drWorkflowAction["rule_id"].ToString(), "target").ToLower();
            if (sBased_On == "user")
            {
                strEmailID = getColumnValue("sy_usr", "af_row_id", strTargetUser, "email_id").ToLower();
                if (!objCmnFun.ValidateEmailID(strEmailID))
                    strEmailID = "";
            }
            else if (sBased_On == "title")
            {
                DataTable dtUserEmails = HDD.GetDataTable("select email_id from sy_usr where title ='" + strTargetUser + "'");
                foreach (DataRow dr in dtUserEmails.Rows)
                {
                    if (objCmnFun.ValidateEmailID(dr["email_id"].ToString()))
                        strEmailID = strEmailID + dr["email_id"].ToString() + ";";
                }
            }
            else if (sBased_On == "user_from_field") // Added by 'Imran Gani' on 21-Mar-2013, to send a email for 'user from field'
            {
                DataRow drEmail = HDD.GetTableRow("select email_id from sy_usr where title ='" + strTargetUser + "'");
                if (drEmail == null)
                    strEmailID = getColumnValue("sy_usr", "af_row_id", strTargetUser, "email_id").ToLower();
                else
                    strEmailID = drEmail["email_id"].ToString();
                if (!objCmnFun.ValidateEmailID(strEmailID))
                    strEmailID = "";
            }

            if (!string.IsNullOrEmpty(strEmailID))
            {
                //objCmnFun.sendMail(strEmailID, strFromMailID, true, strMailContent, strMailSubject);
                bool sendemail = SentInEmailQueue(strEmailID, strFromMailID, true, strMailContent.Replace("'", "''"), strMailSubject.Replace("'", "''"));
            }
            return true;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : sendEmail :" + ex.Message);
            return false;
        }
    }
    /// <summary>
    /// To change the email content with their required data.
    /// </summary>
    /// <param name="dr">Input data row.</param>
    /// <param name="strMailText">Email template.</param>
    /// <returns>Email content</returns>
    private string ConvertMailContent(DataRow dr, string strMailText) //added by 'Imran Gnai' on 22/05/12, for email content.
    {
        foreach (DataColumn dc in dr.Table.Columns)
        {
            if (strMailText.Contains("$" + dc.ColumnName + "$"))
                strMailText = strMailText.Replace("$" + dc.ColumnName + "$", dr[dc.ColumnName].ToString());
        }
        return strMailText;
    }
    
    // Added by 'Imran Gani' on 11-June-2013, to send email from email queue.
    public bool SentInEmailQueue(string sToEmailID, string sFromMailID, bool isHTML, string content, string subject)
    {
        bool sRet = false;
        ScreenReader SR = new ScreenReader();
        SqlConnection SqlCon = null;
        SqlCommand sqlCmd = null;
        try
        {
            string sConn = HttpContext.Current.Session["servercon"].ToString();
            HDDatabase Hdd = new HDDatabase(sConn);

            string strQuery = "INSERT INTO [email_queue] ([to_address],[from_address],[isHTML],[subject],[content],[priority],[delivery],[created_at],[row_id],[bcc_address]) " +
                "VALUES ('" + sToEmailID + "','" + sFromMailID + "'," + (isHTML ? 1 : 0) + ",'" + subject + "','" + content + "',0,'" + DateTime.Now.ToUniversalTime() + "','" + DateTime.Now.ToUniversalTime() + "','" + SR.getHashKey() + "','')";

            SqlCon = new SqlConnection(sConn);
            sqlCmd = new SqlCommand(strQuery, SqlCon);
            int execute = Hdd.ExecuteCmdObj(sqlCmd);
            if (execute > 0) sRet = true;
            Hdd = null;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : SentInEmailQueue :" + ex.Message);
            return false;
        }
        finally
        {
            if (SqlCon != null)
            {
                if (SqlCon.State.Equals(ConnectionState.Open))
                    SqlCon.Close();
                SqlCon.Dispose();
            }
            if (sqlCmd != null)
                sqlCmd.Dispose();
            SR = null;
        }
        return sRet;
    }
    //Up to this
}

